Multi-Column Index Behavior in MySQL with Unused Index Column

Question

Suppose I define the following index on a table in a MySQL database:

(col1, col2, col3)

I know that I get indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

Do I also get indexed search capabilities on (col1, col3)?

Yes, but only for the first-level match on col1. Since col2 isn't being used, col3 will force a table scan on whatever records have the matching col1. It's better than not having an index, but not as good as having full-coverage index. — Marc B
– Marc B, Commented Mar 10, 2015 at 21:28
So in other words, no, not on (col1, col3). Only on (col1) (which will be used for part of the lookup on (col1, col3)). — mkasberg
– mkasberg, Commented Mar 10, 2015 at 21:29
that's another way of looking at it. the col1 matches will be "fast" since the index can be used, but col3 will have to scan the individual rows found by the col1 match. so... partial table scan. — Marc B
– Marc B, Commented Mar 10, 2015 at 21:31
Yes, that's correct. An index is a B-tree. Before it can match the third column, it has to match the first two. — Barmar
– Barmar, Commented Mar 10, 2015 at 21:31
Just adding my €0.02: A partitial table scan can be a very cheap operation, if the majority of rows that match on col1 live in the same or adjacent blocks. This has real-world repercussions, if there are only a few values of col2 a.o.t. many values of col1 — Eugen Rieck
– Eugen Rieck, Commented Mar 10, 2015 at 21:39

paparazzo · Accepted Answer · 2015-03-11 17:58:05Z

1

This is my experience with MSSQL so please test with MySQL

Consider a composite index on (col1, col2, col3)

You get an index seek on:
col1
col1 & col2
col1 & col2 & col3

On col2 and col3 you can get an index (not table) scan.
Since the index is smaller than the table this can help search times.
Some times this is a significant impact.

A search on col1 and col3 would (hopefully) be an index seek on col1 and an index scan on col3.

And note if the table is small you will just get some default plans
Need to load up with some data to test

edited Mar 11, 2015 at 17:58

answered Mar 11, 2015 at 17:39

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

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

1 Comment

Rick James Over a year ago

MySQL is similar. One clarification: SELECT col1, col2, col3 FROM t WHERE col1 = 99 AND col3=88 do a "range scan" in the index without touching the "data". But... SELECT _somethingElse_ FROM t WHERE col1 = 99 AND col3=88 can't stay in the index.

mkasberg · Accepted Answer · 2015-03-11 17:33:45Z

0

Summing up Marc B's answers from the comments:

You do not get full indexed search capabilities on (col1, col3) from the index (col1, col2, col3); however, you will still get the benefits of a (col1) index with indexed search capabilities from the first level match on the col1 portion of the query. A table scan would then be used on col3.

If it is necessary to have full indexed search capabilities on (col1, col3) (it may not be - see Eugen Rieck's comment), you would need a separate index on (col1, col3).

answered Mar 11, 2015 at 17:33

community wiki

mkasberg

Collectives™ on Stack Overflow

Multi-Column Index Behavior in MySQL with Unused Index Column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related