Results 1 to 2 of 2
  1. #1
    Join Date
    May 2014
    Posts
    1

    Unanswered: How to create an index

    Hello,

    I'm fairly new to mysql and trying to create an index on the following join statement.

    select pc.*, pu.*,pr.pick_date,pr.return_date from point_customer pc
    left join point_userinfo pu on (pu.id=pc.id)
    left join point_reservation pr on (pr.cid = pc.cid)
    where pc.reservation = 'booked' and pc.status = 'Active' and ( (pu.date_pick_st >= '2014-05-10 15:00:57' ) && (pu.date_pick_st <= '2014-05-11 3:00:57')
    || (pu.date_return_st >= '2014-05-10 15:00:57' ) && (pu.date_return_st <= '2014-05-11 3:00:57')
    ) ORDER BY pu.date_pick ASC, pu.date_return_df ASC;


    In order to increase the performance, do I need to create a join between "pu.id" and "pc.id"? As well as "pr.cid" and "rc.cid"?

    Would the following work?

    create index point_userinfo_IDX on point_userinfo (id);
    create index point_reservation_IDX on point_reservation (cid);

    Thanks for any help anyone can provide!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it all depends
    why not do some experimentation on your data. take some metrics try to see if there is a significiant improvement
    try to establish if there is a significant bottleneck

    generally speaking its better to index columns FREQUENTLY used in joins. but nothing in life is free. more indexing delays inserts. indexes occasionally need re building
    https://www.google.co.uk/search?q=my...m=122&ie=UTF-8
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •