imscp | i-MSCP Main Repository

When you have the EXPLAIN that ignores the index, it shows it runs a table scan (type: ALL). The rows examined is ~4.1m (which is only a rough estimate anyway, not an accurate count).

You clarified that there are ~4.1m rows with domain_id = 150. So, practically every row in the table matches your WHERE condition.

Think of an index at the back of a book. Why doesn't it contain entries for words like "the" or "and"? Because those words appear on virtually every page, and it would be a waste of time to index them, and to use the index to find occurrences of those common words, flip to the respective page, then flip back to the index to find the next occurrence is on page 2, and so on.

It's the same way with a secondary index in MySQL. If the optimizer detects that a given value you search for is too common, it skips the index and just does a table-scan. It's easier to do that when reading an index when the index won't effectively narrow down the search enough to make it worthwhile.

In practice, I've observed that the optimizer skips using the index when the value occurs on 21-25% of rows in the table. Usually this is a good call. Rarely, it's necessary to use FORCE INDEX to tell the optimizer that you don't want a table-scan at all costs. But that's rare.

My advice is: Let the optimizer do its job. It will usually make a good decision about whether to use an index, based on the query logic and the frequency of the specific value you're searching for.

Re your comment:

If your production data allows the WHERE condition to select a minority subset of your table, then the optimizer should decide that it's worth using the index. One of the optimizer's goals is to reduce the number of examined rows that InnoDB needs to read.

This is a good example of why you need to test with data that mimics your production data. Having the right ratios of different data values helps you do a realistic query optimizer tests.

Also make sure you use ANALYZE TABLE from time to time to make sure InnoDB has current stats about data distribution in the index. I've seen cases where odd index behavior was fixed very simply by running ANALYZE TABLE. It's a quick operation, even if your table is very large.

This doesn't have to be very frequent, but if the distribution of values in your index changes dramatically (like if you do a major bulk insert or bulk delete), it's worth doing ANALYZE TABLE afterwards.