CODEWITHSUNDEEP
mysqlsqlquery-optimization
mkasberg
mkasberg

Reputation: 17342

Multi-Column Index Behavior in MySQL with Unused Index Column

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)?

Upvotes: 1

Views: 111

Answers (2)

paparazzo
paparazzo

Reputation: 45096

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

Upvotes: 1

mkasberg
mkasberg

Reputation: 17342

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).

Upvotes: 0

Related Questions