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

    Unanswered: How to create an index


    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 (
    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 "" and ""? 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
    out on a limb
    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
    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