MySQL indices and order
indexing, mysql, sql-order-by
Solution
Index order matters when your query conditions only apply to PART of the index. Consider:
`SELECT * FROM table WHERE first_name="john" AND last_name="doe"`
`SELECT * FROM table WHERE first_name="john"`
`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.
Problem
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?