Need help to create index on join statments
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