why index not used here in MYSQL?

Question

Very odd as follows:

mysql> explain select *from online where last < now()-interval 30 second and type=1;
+----+-------------+--------+------+---------------------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys                         | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | online | ALL  | i_online_type_last,i_online_last_type | NULL | NULL    | NULL |   24 | Using where |
+----+-------------+--------+------+---------------------------------------+------+---------+------+------+-------------+

mysql> explain select *from online where last < '2009-06-16 06:48:33' and type=1;
+----+-------------+--------+------+---------------------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys                         | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | online | ALL  | i_online_type_last,i_online_last_type | NULL | NULL    | NULL |  120 | Using where |
+----+-------------+--------+------+---------------------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)


mysql> show index from online;
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| online |          0 | PRIMARY            |            1 | id          | A         |          24 |     NULL | NULL   |      | BTREE      |         |
| online |          0 | account_id         |            1 | account_id  | A         |          24 |     NULL | NULL   |      | BTREE      |         |
| online |          1 | i_online_type_last |            1 | last        | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |
| online |          1 | i_online_type_last |            2 | type        | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| online |          1 | i_online_last_type |            1 | last        | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |
| online |          1 | i_online_last_type |            2 | type        | A         |           2 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)

For those who say it's because of table size:

mysql> explain select *from users where email='[email protected]';
+----+-------------+-------+-------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+-------+------+-------+
|  1 | SIMPLE      | users | const | u_users_email | u_users_email | 386     | const |    1 |       |
+----+-------------+-------+-------+---------------+---------------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)

Here are some more clues:

mysql> explain select * from online where `last` > '2009-06-16 06:48:33' and type in (1,2);
+----+-------------+--------+-------+--------------------+--------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys      | key                | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+--------------------+--------------------+---------+------+------+-------------+
|  1 | SIMPLE      | online | range | i_online_type_last | i_online_type_last | 13      | NULL |    2 | Using where |
+----+-------------+--------+-------+--------------------+--------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from online where `last` < '2009-06-16 06:48:33' and type in (1,2);
+----+-------------+--------+------+--------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+--------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | online | ALL  | i_online_type_last | NULL | NULL    | NULL |  120 | Using where |
+----+-------------+--------+------+--------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Change '<' to '>' will make it totally different,why?

At last I found the fix,it's because of last has a default value "null",change this column to "not null" will make index work.

But I've no idea why this can make it different,any explanations?

Answer 1

24 rows isn't enough for the optimizer to bother with. You need to test with a larger table.

Answer 2

• First of all, it's 24 rows - you might be off just because of that;
• Second of all, try removing the reference to now()-interval 30 second to a date literal. I've seen them throw off indexes.

---

Is this me or do you have 2 indexes which are exactly the same?

Answer 3

I have seen the query optimizer in MySQL do some strange things with index selection, and often the only way to find a fix was by trial and error. A couple things to try (without any guarantee that they might help):

• Remove one of the redundant indexes (i_online_*); keep the one where the first column has higher specificity (probably the one with last as the first column).
• Try to see whether making the last column NOT NULL makes a difference (use a minimum date instead of null).
• I second Robert Munteanu's suggestion of trying to replace the now() ... expression. Try to use a variable there that you have set before.

It would also help to see the entire schema of your table; maybe there are some weird side-effects to spot?