MySQL Forums
Forum List  »  Optimizer & Parser

Need help to create index on join statments
Posted by: Neil Sorathia
Date: October 08, 2014 06:24PM

Hello,

I was hoping someone on here could help me out.

I'm trying to optimize my SELECT statement by creating indexes for the below joins. Could someone please help me create indexs on the below SELECT statement?


select pc.cid,pc.fname,pc.lname,pc.cell,pc.home_phone,pc.payment_type,pc.status as del_status, pc.pstatus, pc.pstatus2, pc.reservation, pu.trip_type,pc.email_address, pr.pick_date,pr.return_date,
pr.finalprice1, pr.finalprice2,pr.totalfare, pp.p1, pp.p2, pp.p3, pp.transaction_id,
dr_up.driver_name as driver_name_up, dr_up.driver_email as driver_email_up,
dr_dn.driver_name as driver_name_dn, dr_dn.driver_email as driver_email_dn
from point_customer pc
left join point_userinfo pu on (pu.id=pc.id)
left join point_reservation pr on (pr.cid = pc.cid)
left join driver dr_up on (dr_up.did = pr.did_up)
left join driver dr_dn on (dr_dn.did = pr.did_dn)
left join point_payment pp on (pp.cid = pc.cid)
where (pc.reservation = 'booked' or pc.reservation = 'cancelled') ORDER BY pc.cid DESC LIMIT 0,10;




Below is my inital thought. But, I'm afraid to execute this on a production server, incase I'm wrong wrong:

create index point_userinfo_IDX on point_userinfo(id);
create index point_customer_IDX on point_customer(id);
create index point_customer_IDX2 on point_customer(cid);
create index point_reservation_IDX on point_reservation(cid);
create index driver_IDX on driver(did);
create index point_payment_IDX on point_payment(cid);

Does this look right? Did I miss something?

Any help would be greatly appreciated!

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Need help to create index on join statments
2256
October 08, 2014 06:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.