I'm aware that when you define a MySQL index that the order of the fields matters if the query is only using part of the index, as described here. But if your query is always going to use all columns defined in the index, then does index column order still not matter?
For example, consider a table with these columns:
Any my query of:
SELECT name WHERE gender='M' and age<18
Will there be a difference in performance if I define the index as (gender, age) vs (age, gender) ? Especially considering there will be one field with more unique values than the other.
I've read some tips on Percona's blog here that indicate you should order your index columns on the most selective columns being first, but that's the only place I've heard that.
Anyone know? I'm using the innodb table type.
BONUS: Same question if your index is the primary key. Does it affect the answer?
Thanks so much for your input!