Mysql used key changes between two consecutive EXPLAINs

Question

I have a huge unmaintained innodb table (~1.5billion rows), I just started to try to query it. I write && instead of AND once; and surprise! it uses different key!

 EXPLAIN EXTENDED SELECT * FROM trans_cache WHERE duration = 0 AND status =-7;
+----+-------------+-----------+------+------------------------------+-----------+---------+-------+-----------+----------+-------------+
| id | select_type | table     | type | possible_keys                | key       | key_len | ref   | rows      | filtered | Extra       |
+----+-------------+-----------+------+------------------------------+-----------+---------+-------+-----------+----------+-------------+
|  1 | SIMPLE      | trans_cache | ref  | duration_status,status | status | 2       | const | 219050284 |   100.00 | Using where |
+----+-------------+-----------+------+------------------------------+-----------+---------+-------+-----------+----------+-------------+

And

EXPLAIN EXTENDED SELECT * FROM trans_cache WHERE duration = 0 && status =-7;
+----+-------------+-----------+------+------------------------------+--------------------+---------+-------------+-----------+----------+-------------+
| id | select_type | table     | type | possible_keys                | key                | key_len | ref         | rows      | filtered | Extra       |
+----+-------------+-----------+------+------------------------------+--------------------+---------+-------------+-----------+----------+-------------+
|  1 | SIMPLE      | trans_cache | ref  | duration_status,status | duration_status | 4       | const,const | 214672958 |   100.00 | Using where |
+----+-------------+-----------+------+------------------------------+------------------+---------+-------------+-----------+----------+-------------+

And since they are different, which query should be faster?

EDIT I found that it is doesn't depend on AND or &&. The used key changes randomly in EXPLAIN results.

Answer 1

It sounds like the table is on the cusp of a cutover for the query optimizer between the estimated total rows and estimated rows being looked up.

The use/ignore index syntax exists for situations like this.

The pitfall to this approach is when the table dynamics change in the future such that it's really not the best choice.

Answer 2

OBSERVATION #1

If you look at the choices made in the explain plan, you could be fooled by something we take for granted with InnoDB tables. The InnoDB Storage Engine tends to dive into index pages to guess at cardinalities. I have stated in my earlier posts:

• Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?
• Aug 03, 2011 : When should I rebuild indexes?
• Apr 23, 2012 : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?
• Mar 03, 2013 : The most efficient method to obtain the total entries into a mysql table

If you want a more consistent EXPLAIN plan, you need to add this to your my.cnf:

[mysqld]
innodb_stats_on_metadata = 0

Afterwards, you should run

mysql> SET GLOBAL innodb_stats_on_metadata = 0;

What does innodb_stats_on_metadata = 0 do ? The MySQL Documentation says:

When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics when metadata statements such as SHOW TABLE STATUS or SHOW INDEX are run, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

To change the setting, issue the statement SET GLOBAL innodb_stats_on_metadata=mode, where mode is either ON or OFF (or 1 or 0). Changing this setting requires the SUPER privilege and immediately affects the operation of all connections.

OBSERVATION #2

Since the number of rows in both explain plans are about the same (+/- 5 million), you may as well run the following:

SELECT duration,status,COUNT(1) RowCount
FROM trans_cache GROUP BY duration,status;

This query will give you the distribution of all (duration,status) key combinations. Key combinations with a high row count may not effective and would result in index scans and table scans, given a level playing field (The playing field would be run queries with random key combinations not knowing whether the query will be fast or not).

OBSERVATION #3

Since this table is InnoDB and has two secondary indexes, please keep in mind that nonunique indexes have keys to the Clustered Index embedded. Adding more indexes will not help.

Running ANALYZE TABLE would help if the table is very read-heavy, but would not help if the table is write-heavy.

Running ORDER BY to reorganize InnoDB is useless.

Running OPTIMIZE TABLE is also useless.

YOUR ACTUAL QUESTION

And since they are different, which query should be faster?

The second explain plan is what you want since the both duration and status are indexed together. You could run USE INDEX to force (not my personal preference).

If the cardinality of (duration 0,status -7) is nearly the whole table, forcing an index may become a rather moot point if the MySQL Query Optimizer and InnoDB Storage Engine work together and come up with the EXPLAIN plan they feel is best.