CODEWITHSUNDEEP
mysqlsqlindexing
Amit
Amit

Reputation: 6304

MySQL Not Using the Best Index

I have a simple select query:

SELECT *
FROM `inventory`
WHERE account_id = "1"
AND marketplace_id = "2"
AND sku = "3"
AND `date` = "4"

It has a condition in four fields, therefore, the natural index is of these four fields.

I have the following indexes: enter image description here

and when I explain the query, I get: enter image description here Saying it is using the 2nd index from the list, rather than the 3rd or 4th.

If I use USE INDEX(account_id__marketplace_id__sku__date) in my query, it successfully uses it, and filters 100%.

I have tried ANALYZE TABLE and OPTIMIZE TABLE to rebuild the indexes, but the situation stays the same!

From my experience with other tables, if I now drop indexes 2 and 3, and recreate them, such that the largest index is the first in the order, the query will select that index, meaning, it selects the first that matches rather a maximal match.

Upvotes: 2

Views: 231

Answers (2)

Hopes Shi
Hopes Shi

Reputation: 26

Mysql will choose index by the table's data size; enter image description here

You can try to insert 600000 records into inventory, keep record's value random, and run "Explain"

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Your sample code has:

date = "1"

That is very arcane, because "1" is not a valid date. What I notice is no indexes use date. My guess is that you have a type conversion issue -- and converting types (and function calls) can prevent the use of indexes.

I would be sure that all the things being compared to are the right type.

Upvotes: 1

Related Questions