NYCPHP Meetup

[Rob Marscher]

On Mar 23, 2010, at 10:16 AM, Daniel Convissor wrote:
> On Mon, Mar 22, 2010 at 01:42:14PM -0400, Rob Marscher wrote:
>> 
>> One caveat to this is that mysql will only use one index per table in 
>> your query.
> 
> I've never heard that before.  Do you have a citation from the manual on 
> MySQL's website, please?


I'm having trouble finding exactly where it says it.  The language on this page touches on it, but the way it's written it seems to assume that you already know it's only going to pick one index per table:
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

If you run EXPLAIN, you'll see it only picks one index to use for each table.

Technically though, there is the "Index Merge" optimization introduced in MySQL 5 that gets it to use more than one index under certain conditions:
http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html

And don't forget indexes can be made on multiple columns:
http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html