CODEWITHSUNDEEP
mysqldatabaseindexing
kkkkkkk
kkkkkkk

Reputation: 7748

Mysql - optimal indexing for query using IN operator

I have this query:

SELECT *
FROM table1
WHERE foo IN (1,2,3,4) AND bar = 123;

I have 3 indices:

ALTER TABLE table1 ADD INDEX IDX_foo (foo);

ALTER TABLE table1 ADD INDEX IDX_bar (bar);

ALTER TABLE table1 ADD INDEX IDX_foo_bar (foo, bar);

When I try EXPLAIN with the query, IDX_bar is chosen which I don't understand why. So my questions are:

Upvotes: 0

Views: 151

Answers (1)

Rick James
Rick James

Reputation: 142298

Use INDEX(bar, foo). That is, put the one with the = first.

Older versions of MySQL had less smarts. The above index is likely to be the best, or at least tie with the best, for all versions of MySQL.

General lesson on building MySQL indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Your specific questions:

  • Why is IDX_bar chosen but not IDX_foo or IDX_foo_bar? -- = is the best for optimizing.

  • Does MySQL choose index differently depend on the data at the time? -- Yes (but not very often). For example, if bar is infrequently 123, any index starting with bar is likely to be useful. If it is frequently 123, then the bouncing back and forth between the index and the data may be more costly than the benefit from the index. (The cutoff varies, but is typically around 20%.)

  • Should I only have IDX_bar and remove the other two? -- Toss your 3, keep mine. However, some other query may benefit from one of yours.

Note: INDEX(bar) is redundant if you have INDEX(bar, foo); get rid of the former.

(Most of what I said here is in that link.)

Upvotes: 1

Related Questions