Index Usage on Multiple Columns

Question

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?

I would guess that in the lack of more specific indexes it uses your index with more dimension. I do not know, just an educated guess. — Lajos Arpad
– Lajos Arpad, Commented Jan 24, 2015 at 16:44

O. Jones · Accepted Answer · 2015-01-24 18:06:37Z

1

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

But two things jump out:

Both queries must take all rows in the table into account (you don't have a WHERE clause).
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.

answered Jan 24, 2015 at 18:06

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kohjah Breese Over a year ago

Thanks. Yes. The explain output for both is identical. I was just under the impression the index shouldn't be used.

Collectives™ on Stack Overflow

Index Usage on Multiple Columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related