I have the following query.
SELECT *
FROM user u
LEFT JOIN operator o ON o.id = u.id
WHERE u.user_type_id IN (2,4) AND u.is_enabled = 1 AND u.office_id = 225
If I run explain on the query above, it shows that it uses the index IX_user_type for the table user.
If I just change the office_id comparison value like the following, the execution plan changes.
SELECT *
FROM user u
LEFT JOIN operator o ON o.id = u.id
WHERE u.user_type_id IN (2,4) AND u.is_enabled = 1 AND u.office_id = 32365487
In this case, the explain shows that the indexes used for the table user are fk_user_office,IX_user_is_enabled.
I made some tests and would say, performance wise, the first execution plan is much better than the second one. Now, I know I can force Mysql to use the index I want but, I would like to understand why this happens. Why would Mysql pick an index instead of another based on a query parameter?
MySQL may decide not to use the index on office_id if the value you are searching for is too common.
By analogy, why doesn't a book include common words like "the" in the index at the back of the book? Because such common words occur on a majority of pages in the book. It's unnecessary to keep a list of those pages under the respective word in the index, because it's easier to tell the reader to read all the pages in the book, without the index lookup.
Similarly, if MySQL estimates that a given value you are searching for occurs on a high enough portion of the pages, it looks for another index if you have other conditions, and if none are found, then it resorts to a table-scan.
In this case, I'd ask if you can confirm that office_id 225 is very common in this table.
One more thought: The best index of all for the query you show would be a compound index on (office_id, is_enabled, user_type). Then it would be able to use that index to narrow down the search by all three columns at once.
You might like my presentation How to Design Indexes, Really or the video. I also have a chapter on index design in my book SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming.