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.

Answer 2

ChssPly76 is correct that the order of boolean expressions does not have to match the order of columns in the index. Boolean operators are commutative, and the MySQL optimizer is smart enough to know how to match the expression to the index in most cases.

I also want to add that you should learn how to use the EXPLAIN feature of MySQL so you can see for yourself which indexes the optimizer will choose for a given query.