indexing - MySQL indices and order

Question

This is a question that I've had forever.

As far as I know the order of indices matter. So an index like [first_name, last_name]?is not the same as [last_name, first_name], right?

If I only define the first index, does it mean that it will only used for

SELECT * FROM table WHERE first_name="john" AND  last_name="doe"; 

and not for

SELECT * FROM table WHERE  last_name="doe" AND first_name="john";

Since I am using a ORM, I have no idea in which order these columns are going to be called. Does that mean that I have to add indices on all permutations? That is doable if I have a 2 column index, but what happens if my index is on 3 or 4 columns?

Answer 1

Index order matters when your query conditions only apply to PART of the index. Consider:

1. SELECT * FROM table WHERE first_name="john" AND last_name="doe"

2. SELECT * FROM table WHERE first_name="john"

3. SELECT * FROM table WHERE last_name="doe"

If your index is (first_name, last_name) queries 1 and 2 will use it, query #3 won't. If your index is (last_name, first_name) queries 1 and 3 will use it, query #2 won't. Changing the condition order within WHERE clause has no effect in either case.

Details are here

Update:
In case the above is not clear - MySQL can only use an index if the columns in query conditions form a leftmost prefix of the index. Query #2 above can not use (last_name, first_name) index because it's only based on first_name and first_name is NOT the leftmost prefix of the (last_name, first_name) index.

The order of conditions WITHIN the query does not matter; query #1 above will be able to use (last_name, first_name) index just fine because its conditions are first_name and last_name and, taken together, they DO form a leftmost prefix of (last_name, first_name) index.