MySQL Forums
Forum List  »  Performance

Re: multi-part index and when it is used?
Posted by: Harrison Fisk
Date: January 12, 2005 09:21PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: multi-part index and when it is used?
2713
January 12, 2005 09:21PM


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.