Re: Order in compote index & performance [ WAS: OR, IN & index usage]

[<sinisa]

Andy Leung writes:
 > 
 > Hi,
 > 
 > I understand that the order of the conditions in the WHERE clause of a query
 > is irrelevant as the server rearranges them. But I was talking about the
 > order of the elements in a composite index, and surely the server would not
 > rearrange that order!
 > 
 > What I was asking was whether that order in a composite index has any effect
 > on performance. For example say I have a query:
 > 
 > SELECT *
 > FROM table
 > WHERE col_1 > "A"
 >   AND col_2 IN ("X", "Y", "Z")
 > 
 > with an index on (col_1, col_2)
 > 
 > Now suppose 90% of the rows have col_1 > "A" while only 5% of the rows have
 > col_2 IN ("X", "Y", "Z"). I don't know how exactly the index B-tree is being
 > searched, but I was wondering would changing the order of the index so that
 > the more "discriminating" column (col_2) comes first would make the query
 > faster. That is:
 > 
 > SELECT *
 > FROM table
 > WHERE col_2 IN ("X", "Y", "Z")
 >   AND col_1 > "A"
 > 
 > with an index on (col_2, col_1)
 > 
 > Would the above scenerio be any faster?
 > 
 > Thanks a lot!
 > 
 > Andy
 > 

Hi!

Yes, index would be used in that case too, providing that the above
conditions do not cover a large range of rows.

MySQL server will not just sort WHERE clauses, but also values in  IN
(..) clause.

Regards,

Sinisa

+----------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___     ==  mysql@stripped            |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sinisa Milivojevic          |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sinisa@stripped     |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Larnaka, Cyprus             |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                             |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                            |
|             /*/             \*\                Developers Team       |
+----------------------------------------------------------------------+