MySQL Forums
Forum List  »  Optimizer & Parser

Re: EXPLAIN Table reference order
Posted by: Rick James
Date: May 28, 2009 07:10PM

Without knowing the indexes, table sizes, etc, I can only guess
select  count(*)
    from  exp1 AS T1
        JOIN  exp2 AS T2 ON T1.ip=T2.ip
    WHERE  (T1.severity LIKE ('critical%')
      AND  (T2.main="All"
      OR  T2.state="All"
      OR  T2.city="All"
      OR  T2.ip="All")
      AND  T2.city='Banglore')

Without the Bangalore test there is nothing but an OR for T2. Since OR is hard to optimize, it would probably decide to do a table scan on T2. Hence, the T1.severity test is probably more select. Hence, T1 is used first.

With the Bangalore test, it seems to have used the index on T2.city first, thereby cutting down on the number of probes needed into the other table.

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
* EXPLAIN SELECT ...\G
and surround them with [ code ] and [ / code ]

Options: ReplyQuote


Subject
Views
Written By
Posted
4828
May 27, 2009 01:06AM
Re: EXPLAIN Table reference order
2302
May 28, 2009 07:10PM


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.