Thread: How to create an index
05-10-14, 19:01 #1Registered User
- 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 (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!
05-12-14, 16:25 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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-8I'd rather be riding on the Tiger 800 or the Norton