Does Order of Fields of Multi-Column Index in MySQL Matter

by

When discussing multi-column indexes, I use an analogy to a telephone book. A telephone book is basically an index on last name, then first name. So the sort order is determined by which “column” is first. Searches fall into a few categories:

  1. If you look up people whose last name is Smith, you can find them easily because the book is sorted by last name.

  2. If you look up people whose first name is John, the telephone book doesn’t help because the Johns are scattered throughout the book. You have to scan the whole telephone book to find them all.

  3. If you look up people with a specific last name Smith and a specific first name John, the book helps because you find the Smiths sorted together, and within that group of Smiths, the Johns are also found in sorted order.

If you had a telephone book sorted by first name then by last name, the sorting of the book would assist you in the above cases #2 and #3, but not case #1.

That explains cases for looking up exact values, but what if you’re looking up by ranges of values? Say you wanted to find all people whose first name is John and whose last name begins with ‘S’ (Smith, Saunders, Staunton, Sherman, etc.). The Johns are sorted under ‘J’ within each last name, but if you want all Johns for all last names starting with ‘S’, the Johns are not grouped together. They’re scattered again, so you end up having to scan through all the names with last name starting with ‘S’. Whereas if the telephone book were organized by first name then by last name, you’d find all the Johns together, then within the Johns, all the ‘S’ last names would be grouped together.

So the order of columns in a multi-column index definitely matters. One type of query may need a certain column order for the index. If you have several types of queries, you might need several indexes to help them, with columns in different orders.

You can read my presentation How to Design Indexes, Really for more information, or the video.

More Related Contents:

  1. MYSQL OR vs IN performance
  2. SQL Performance UNION vs OR
  3. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  4. MySQL “IN” operator performance on (large?) number of values
  5. What is better in MYSQL count(*) or count(1)?
  6. Mysql Improve Search Performance with wildcards (%%)
  7. DOs and DONTs for Indexes [closed]
  8. MySQL performance: multiple tables vs. index on single table and partitions
  9. Mysql covering vs composite vs column index
  10. MySQL: Inner join vs Where [duplicate]
  11. Higher cardinality column first in an index when involving a range?
  12. MySQL load data infile – acceleration?
  13. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  14. MySQL optimizing INSERT speed being slowed down because of indices
  15. Would it help to add index to BIGINT column in MySQL?
  16. Two single-column indexes vs one two-column index in MySQL?
  17. MySQL – why not index every field?
  18. Mysql performance on 6 million row table
  19. Create a Cumulative Sum Column in MySQL
  20. Equivalent of explode() to work with strings in MySQL
  21. mysql select dynamic row values as column names, another column as value
  22. MySQL DELETE FROM with subquery as condition
  23. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  24. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  25. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  26. Simple Random Samples from a Sql database
  27. Checking multiple columns for one value
  28. How to enable INNODB in mysql
  29. MySQL GROUP BY DateTime +/- 3 seconds
  30. #1060 – Duplicate column name ‘id’

Leave a Comment