Does the order of a mysql multi-column MySQL index matter - if you're always using all columns in the query? 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:


name
gender
age


Any my query of:

SELECT name WHERE gender='M' and age&lt;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!
In your case it's a choice between bad and worse. gender is low selective (read MySQL has to read at least a half of index), if age comes first, gender part won't be used at all (because of &lt; ). (gender, age) seems to be a bit better anyway.

Check with SHOW STATUS LIKE 'Handler%' which index makes MySQL to read less rows.

mysql&gt; FLUSH STATUS;
mysql&gt; SELECT name WHERE gender='M' and age&lt;18;
mysql&gt; mysql&gt; show status like 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 38    |
| Handler_delete             | 8     |
| Handler_discover           | 0     |
| Handler_external_lock      | 154   |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 35    |
| Handler_read_key           | 62    |
| Handler_read_last          | 0     |
| Handler_read_next          | 153   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 5947  |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 1     |
| Handler_write              | 617   |
+----------------------------+-------+