MySQL Forums
Forum List  »  Optimizer & Parser

Re: Need help to create index on join statments
Posted by: Rick James
Date: October 11, 2014 01:37PM

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;

Let's walk through it.
You can do "EXPLAIN SELECT ..." to see in what order the tables are _currently_ used. In lieu of that, and in the absence of SHOW CREATE TABLEs, I will walk through what is _likely_ to be optimal...

1. Either the WHERE clause or the ORDER BY will be how it gets started.

But, there is already a potential problem. Do you get duplicate rows? That might happen because of all the JOINs.
Do you get NULLs? That is because of LEFT JOIN. But maybe you want the NULLs? Please decide whether you want/need LEFT -- it impacts the optimization, and hence the indexes.

The optimizer might start with
WHERE pc.reservation IN ('booked', 'cancelled')
which would like INDEX(reservation). However, the cardinality is that field is probably such that the index won't help. What percentage of rows are booked or cancelled?

Or the optimizer might decide that "ORDER BY pc.cid DESC" is the best starting point. (Before 5.7, the LIMIT 10 is not taken into account, even though that would say that the ORDER BY is probably the best way to go.) Do you have PRIMARY KEY(cid)? Is the table InnoDB? Is this a single system, not a multi-master or Galera based? I ask this last one because you seem to be depending on the cids being chronological.

LIMIT 0, 10 smells like "pagination using offset and limit". This has serious problems in scaling; but that is another topic.

2. All the other tables are LEFT JOIN'd -- this usually (not always) forces the optimizer to use the table to the left first. Assuming that, then it is simple to decide what indexes are needed for the rest. For example,
FROM pc LEFT JOIN point_userinfo pu on (pu.id = pc.id)
says that pu.id should be indexed. Probably in the form of PRIMARY KEY(id).
(I need to see SHOW CREATE TABLE to see if id is already the PK, or whether it is something else.)

Please note that a PRIMARY KEY is a UNIQUE key, which is an INDEX. So, if you already have PRIMARY KEY(id), do _not_ CREATE another INDEX on (id). (Again, the SHOW CREATE TABLE would have answered this issue.)

We have discussed the example you gave. However minor changes to the WHERE clause and "LEFT" might radically change the optimization issues. Please look around to see if there are other examples so we can factor them in.

For example, if you said
WHERE pr.return_date = '...'
then the optimizer would want to _start_ with pr. This, plus
left join point_reservation pr on (pr.cid = pc.cid)
would mean that INDEX(cid) would be useful on pc. That is, each ON clause is potentially flippable.

Bottom line: I won't even say "maybe" your CREATE INDEXes are right. However, I hope you have some more insight on how to create indexes from a SELECT. Feel free to continue this thread with answers to my requests. I will provide more insight.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Need help to create index on join statments
1160
October 11, 2014 01:37PM


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.