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='test@gmail.com';
+----+-------------+-------+-------+---------------+---------------+---------+-------+------+-------+
| 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.
最后我找到了修复,这是因为last有一个默认值“null”,将此列更改为“not null”将使索引工作。
But I've no idea why this can make it different,any explanations?
但是我不知道为什么这会让它变得不同,有什么解释吗?
3 个解决方案
#1
24 rows isn't enough for the optimizer to bother with. You need to test with a larger table.
24行不足以让优化器烦恼。您需要使用更大的表进行测试。
#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 secondto a date literal. I've seen them throw off indexes.
首先,它是24行 - 你可能因此而离开;
其次,尝试删除对now()的引用 - 间隔30秒到日期文字。我看到他们扔掉了索引。
Is this me or do you have 2 indexes which are exactly the same?
这是我还是你有两个完全相同的索引?
#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):
我已经看到MySQL中的查询优化器通过索引选择做了一些奇怪的事情,并且通常找到修复的唯一方法是通过反复试验。有几件事要尝试(不保证他们可能有所帮助):
- Remove one of the redundant indexes (
i_online_*); keep the one where the first column has higher specificity (probably the one withlastas the first column). - Try to see whether making the
lastcolumnNOT NULLmakes a difference (use a minimum date instead ofnull). - I second Robert Munteanu's suggestion of trying to replace the
now() ...expression. Try to use a variable there that you have set before.
删除其中一个冗余索引(i_online_ *);保持第一列具有更高特异性的那一个(可能是最后一列作为第一列的那个)。
尝试查看最后一列NOT NULL是否有所不同(使用最小日期而不是null)。
我的第二个Robert Munteanu建议尝试替换now()...表达式。尝试使用之前设置的变量。
It would also help to see the entire schema of your table; maybe there are some weird side-effects to spot?
它也有助于查看表的整个模式;也许有一些奇怪的副作用要发现?
#1
24 rows isn't enough for the optimizer to bother with. You need to test with a larger table.
24行不足以让优化器烦恼。您需要使用更大的表进行测试。
#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 secondto a date literal. I've seen them throw off indexes.
首先,它是24行 - 你可能因此而离开;
其次,尝试删除对now()的引用 - 间隔30秒到日期文字。我看到他们扔掉了索引。
Is this me or do you have 2 indexes which are exactly the same?
这是我还是你有两个完全相同的索引?
#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):
我已经看到MySQL中的查询优化器通过索引选择做了一些奇怪的事情,并且通常找到修复的唯一方法是通过反复试验。有几件事要尝试(不保证他们可能有所帮助):
- Remove one of the redundant indexes (
i_online_*); keep the one where the first column has higher specificity (probably the one withlastas the first column). - Try to see whether making the
lastcolumnNOT NULLmakes a difference (use a minimum date instead ofnull). - I second Robert Munteanu's suggestion of trying to replace the
now() ...expression. Try to use a variable there that you have set before.
删除其中一个冗余索引(i_online_ *);保持第一列具有更高特异性的那一个(可能是最后一列作为第一列的那个)。
尝试查看最后一列NOT NULL是否有所不同(使用最小日期而不是null)。
我的第二个Robert Munteanu建议尝试替换now()...表达式。尝试使用之前设置的变量。
It would also help to see the entire schema of your table; maybe there are some weird side-effects to spot?
它也有助于查看表的整个模式;也许有一些奇怪的副作用要发现?