Re: multi-part index and when it is used?

[Harrison Fisk]

Hi,

MySQL can use any left prefix of an index as it if was a normal index. So if you had (a,b,c) and used WHERE a = 5 or WHERE a LIKE 'foo%' then it would use the index. If it was WHERE b = 5, then it wouldn't.

As far as your idea about removing ORDER BY, that doesn't make any sense. If MySQL notices that the data will be in order, then the ORDER BY will effectively be free. So always list the ORDER BY that you want.

The only query you listed that might not use the index is SELECT * FROM phonebook; The reason that one might not is because it retrieves too much data. The only way it would use an index would be if the index was a covering index (ie. * was all part of the same index). Then it could perform a full index scan, otherwise it would do a full table scan and sort afterwords, since it is faster.

Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com