CODEWITHSUNDEEP
mysqlindexing
Kohjah Breese
Kohjah Breese

Reputation: 4136

Index Usage on Multiple Columns

I have a table that contains 250 million records recording people who live in the US and their sate, county and settlement. A simplified version looks like:

mysql

I have put a combined index on surname, region, subregion and place. The following queries execute in exactly the same time:

SELECT SQL_NO_CACHE surname, place, count(*) as cnt FROM `ustest` group by place, surname;

SELECT SQL_NO_CACHE surname, region, count(*) as cnt FROM `ustest` group by region, surname;

I was under the impression that the first query would not use the index, as I thought that to use an index you had to query on all the columns from left to right.

Can anyone explain how MySQL uses indexes on multiple columns in such instances?

Upvotes: 2

Views: 40

Answers (1)

O. Jones
O. Jones

Reputation: 108641

It's hard to tell the specifics of your queries' execution plans without seeing the EXPLAIN output.

But two things jump out:

  1. Both queries must take all rows in the table into account (you don't have a WHERE clause).
  2. Both queries could be satisfied by scanning your compound index based on surname being the lead column of that index. Because you're counting items, it's necessary to do a tight, not loose, index scan. (You can read about those.)

So it's possible that they both have the same execution plan.

Upvotes: 1

Related Questions