2

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<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!

migrated from serverfault.com Nov 12 '14 at 20:13

This question came from our site for system and network administrators.

3

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 < ). (gender, age) seems to be a bit better anyway.

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

mysql> FLUSH STATUS;
mysql> SELECT name WHERE gender='M' and age<18;
mysql> mysql> 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   |
+----------------------------+-------+
  • So you're saying yes, that it does matter and most selective should be first? BTW the age/gender is just a hypothetical example of fields with differing cardinality. – Shane N Nov 12 '14 at 20:39
  • yes. the goal of indexes is to read as little as possible from table, yet to satisfy the query. if high selective field comes first then MySQL will read less rows. – akuzminsky Nov 12 '14 at 20:42
  • Got it - thanks. Not sure why I can't find that in the mysql docs anywhere, unless it's just common sense ;-) – Shane N Nov 12 '14 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.