Chris wrote: > Roberto Mansfield wrote: >> It shouldn't matter what order the columns are referenced. Mysql is >> smart enough to optimize the query based on the available indexes. > > In some cases yes but as with anything there are exceptions :) > > mysql (and every other db) gets it wrong sometimes. > >> In fact, it should be good enough just to create an index on each column >> that will be searched -- not on combinations of columns. > > Multicolumn indexes definitely have their uses. But as the OP found out, > they are read left to right based on the idx definition. > > http://dev.mysql.com/doc/refman/5.1/en/multiple-column-indexes.html > http://www.postgresql.org/docs/8.2/interactive/indexes-multicolumn.html > > are two documents explaining this. My point here was the if you index on (a, b), you don't need to index on (b, a) if both a and b are present in your where clause. The index is read from left to right -- not the where clause. >> Do you have any performance numbers to believe that this is not the case? > > Mysql will actually only use one index per table. I was surprised to > find this out but it's mentioned in > http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/dp/0596003064/ > - page 64 (just looked it up to include a page ref). > > No idea if this is mentioned anywhere on the mysql site (doubt it). This is the case in 4.x and earlier. In 5.x and later, mysql can use multiple indexes per table in a query. You can verify this with EXPLAIN assuming the optimizer considers using multiple indexes to be fastest. (Sometimes, one restriction will limit the result considerably and using multiple indexes isn't necessary.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
