Declaring indexes together or separately, what is the difference?

Question

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?

Answer 1

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.