Description:
The engine chooses the wrong index in a simple select query.
How to repeat:
My case:
mysql> select ID from t_e where e=2781 and p=23927 and ff is null;
+-----------+
| ID |
+-----------+
| 130419083 |
+-----------+
1 row in set (10.36 sec)
mysql> explain select ID from t_e where e=2781 and p=23927 and ff is null;
+----+-------------+--------+------+--------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+--------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------------+
| 1 | SIMPLE | t_e | ref | E_V,E_P,E_EF,E_PERS,E_VH,E_V_F | E_V | 4 | const | 10 | Using where with pushed condition |
+----+-------------+--------+------+--------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------------+
1 row in set (0.00 sec)
E_V is an index containing the (e,p,fi) columns, where fi is a datetime column that doesn't appear in the query.
In InnoDB, the query is fast and the explain is as following:
mysql> explain select ID from t_e where e=2781 and p=23927 and ff is null;
+----+-------------+--------+------+--------------------------------------------------------------------------------------------+-----------------+---------+-------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+--------------------------------------------------------------------------------------------+-----------------+---------+-------------------+------+--------------------------+
| 1 | SIMPLE | t_e | ref | E_V,E_P,E_EF,E_PERS,E_VH,E_V_F | E_PERS | 18 | const,const,const | 5 | Using where; Using index |
+----+-------------+--------+------+--------------------------------------------------------------------------------------------+-----------------+---------+-------------------+------+--------------------------+
1 row in set (0.00 sec
E_PERS is an index containing the (e,p,ff) columns, and this should be the obvious index choice for this query because it contains the 3 columns of the query.
Back to the cluster, if we force the index, everything is ok:
mysql> select ID from t_e force index(e_pers) where e=2781 and p=23927 and ff is null;
+-----------+
| ID |
+-----------+
| 130419083 |
+-----------+
1 row in set (0.01 sec)
e and p are ints, ff and fi are datetimes.
If we change the condition "ff is null" to ff="2010-04-29 20:31:10", same thing.
--------------------------------
Easy way to repeat it:
mysql> create table t (id int primary key, e int, p int, fi datetime, ff datetime) type=ndbcluster;
Query OK, 0 rows affected, 1 warning (0.45 sec)
mysql> create index t_idx1 on t (e,p,fi);
^[[AQuery OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index t_idx2 on t (e,p,ff);
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t (id,e,p,fi,ff) values(1,2,3,'2010-04-29 20:31:10','2010-04-29 20:31:10');
Query OK, 1 row affected (0.01 sec)
mysql> explain select id from t where e=2781 and p=23927 and ff is null;
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------------------+
| 1 | SIMPLE | t | ref | t_idx1,t_idx2 | t_idx1 | 10 | const,const | 10 | Using where with pushed condition |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------------------+
1 row in set (0.00 sec)
mysql> explain select id from t where e=2781 and p=23927 and ff='2010-04-29 20:31:10';
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------------------+
| 1 | SIMPLE | t | ref | t_idx1,t_idx2 | t_idx1 | 10 | const,const | 10 | Using where with pushed condition |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------------------+
1 row in set (0.00 sec)
