CODEWITHSUNDEEP
mysqldatabaseperformanceindexing
Jonathan
Jonathan

Reputation: 15452

MySQL not using optimum composite key

I have a table which I've added a couple of composite keys to:

CREATE INDEX idx_foo on my_table(col_one, col_two, col_three);

CREATE INDEX idx_bar on my_table(col_one, col_four, col_three);

Why, when I use EXPLAIN on the following query, does MySQL tell me it's using the less-appropriate idx_bar key?

SELECT col_one, col_two, col_three, col_four FROM my_table
WHERE col_one = 'a'
AND col_two = 1
AND col_three = 1

returns:

'1', 'SIMPLE', 'my_table','ref','IDX_E93438563DAE168B,idx_foo,idx_bar','idx_bar','767','const','6688','Using index condition; Using where'

Am I overestimating MySQL's matching engine, or have I made a mistake? Is the only solution to use FORCE INDEX?

Upvotes: 2

Views: 45

Answers (1)

Rick James
Rick James

Reputation: 142453

Wait! You say that col_one is UNIQUE? Then any index starting with col_one will find the answer in one try!

So, here's why it picked the 'wrong' index: It is smaller.

Upvotes: 1

Related Questions