10

I'm having trouble with huge datasets in mysql and I'm exploring lots of different ways to index. Can anyone tell me what the difference is if I declare several indexes together

ALTER TABLE `db`.`test` ADD INDEX `someindex` (field1, field2, field3);

Rather than declaring them separately?

ALTER TABLE `db`.`test` ADD INDEX `f1` (field1), ADD INDEX `f2` (field2);

Why would one want to declare them together or separately?

Matt Ball
  • 332,322
  • 92
  • 617
  • 683
jwillis0720
  • 3,464
  • 6
  • 31
  • 64
  • 1
    You are only creating 1 index in the first statement. It would be like searching a phone book based on last_name, first_name, initial. The second statement would be like two phone books: one by last_name and another book by first_name. – Glenn Dec 17 '12 at 01:05
  • [See the answers on this question. It mentioned their about indexes.](http://stackoverflow.com/questions/13892952/database-schema-confusing-index-and-constraints) – John Woo Dec 17 '12 at 01:11

2 Answers2

11

I teach MySQL training classes, and 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.

For more details and examples, see my presentation How to Design Indexes, Really. Or watch my presentation on video.

To clarify when to use a single-column index vs. a multi-column index, consider if you are using your phone book to look up a person by the combination of last name and first name. For example "Sarah Smith."

If you had two phone books, one organized by last name and the other organized by first name, you could search the last name book for "Smith" and search the first name book for "Sarah" and then somehow find the intersection of the two results. MySQL sometimes tries to do this with the index merge algorithm.

It would be better to search one index, if it's sorted by both last name and first name, like a real phone book. Then the search finds the subset of the book for "Smiths" and within that subset, it can efficiently search for "Sarahs" because the subset is sorted by first name.

Bill Karwin
  • 462,430
  • 80
  • 609
  • 762
  • This answer addresses the order in which you put things into a multi-column index, not when to use a single column vs multi-column. It should clarify any limitations/advantages of using a single column index. – Eric G Mar 18 '19 at 20:15
  • To help what I mean, in another answer, it is implied that multiple single column indexes can offer much of the benefit of multi-column indexes: https://stackoverflow.com/a/12222699/457836 in which case I feel like I might want three single column indexes if I may also need to reference those columns seperately? – Eric G Mar 18 '19 at 20:19
2

Databases can typically only use one index per query, so assuming that all three columns are in your 'where' clause, you would want the single, compound index.

However, compound indexes can only be used partially from left to right, so if you have another query on, say, just field1, then the compound index will still be used. But, for a query with only field2 in the 'where' clause, that index cannot be used, and you will need an index that is either just on field2, or one which is compound, but starts with field2.

This is explained in detail in the [MySQL documentation]

GreyBeardedGeek
  • 26,236
  • 2
  • 39
  • 58