mysql> explain select a.id,a.title from users c
-> straight_join iask a on c.id=a.uid
-> straight_join ianswer b on a.id=b.iaskid
->
-> where (c.last_check is null or b.created>c.last_check) and c.id in (1,2)
-> group by a.id;
+----+-------------+-------+-------+---------------------------+------------+---------+----------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------------+------------+---------+----------+------+----------------------------------------------+
| 1 | SIMPLE | c | range | PRIMARY,i_users_lastcheck | PRIMARY | 4 | NULL | 2 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | PRIMARY,i_iask_uid | i_iask_uid | 4 | bbs.c.id | 2 | |
| 1 | SIMPLE | b | ALL | i_ianswer_iaskid | NULL | NULL | NULL | 17 | Using where; Using join buffer |
+----+-------------+-------+-------+---------------------------+------------+---------+----------+------+----------------------------------------------+
3 rows in set (0.00 sec)
change the above "in (1,2)" into "=1" will make it all using index:
将上面的“in(1,2)”更改为“= 1”将使所有使用索引:
mysql> explain select a.id,a.title from iask a
-> join ianswer b on a.id=b.iaskid
-> join users c on c.id=a.uid
-> where (c.last_check is null or b.created>c.last_check) and c.id=1
-> group by a.id;
+----+-------------+-------+-------+---------------------------+------------------+---------+----------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------------+------------------+---------+----------+------+---------------------------------+
| 1 | SIMPLE | c | const | PRIMARY,i_users_lastcheck | PRIMARY | 4 | const | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | PRIMARY,i_iask_uid | i_iask_uid | 4 | const | 1 | Using where |
| 1 | SIMPLE | b | ref | i_ianswer_iaskid | i_ianswer_iaskid | 4 | bbs.a.id | 8 | Using where |
+----+-------------+-------+-------+---------------------------+------------------+---------+----------+------+---------------------------------+
3 rows in set (0.00 sec)
table structure as follows:
表结构如下:
mysql> show create table users;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| users | CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(128) NOT NULL,
`password` varchar(32) NOT NULL,
`screen_name` varchar(64) DEFAULT NULL,
`reputation` int(10) unsigned NOT NULL DEFAULT '0',
`imtype` varchar(1) DEFAULT '0' COMMENT '0--email,1--gtalk,2--msn',
`last_check` datetime DEFAULT NULL,
`robotno` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `u_users_email` (`email`),
UNIQUE KEY `u_users_screen_name` (`screen_name`),
KEY `i_users_lastcheck` (`last_check`),
KEY `i_users_imtype_robotno` (`imtype`,`robotno`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.02 sec)
mysql> show create table iask;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| iask | CREATE TABLE `iask` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(10) unsigned NOT NULL,
`title` varchar(250) NOT NULL,
`body` text,
`tags` varchar(100) NOT NULL,
`views` int(10) unsigned DEFAULT '0',
`votes` int(10) unsigned DEFAULT '0',
`answer_id` int(10) unsigned DEFAULT NULL,
`created` datetime NOT NULL,
`keywords` text,
PRIMARY KEY (`id`),
KEY `i_iask_uid` (`uid`),
FULLTEXT KEY `keywords` (`keywords`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
mysql> show create table ianswer;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ianswer | CREATE TABLE `ianswer` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`iaskid` int(10) unsigned NOT NULL,
`uid` int(10) unsigned DEFAULT NULL,
`body` text,
`votes` int(10) unsigned DEFAULT '0',
`created` datetime NOT NULL,
`anonymous` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `i_ianswer_iaskid` (`iaskid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
1 个解决方案
#1
If you are asking how to make the first query use an index like the second query does, I suspect it is because there aren't enough rows in your table to justify two index lookups. The row count for a full table scan is 17 rows, so it thinks it is faster just to spin across the rows looking for two ids. If you are concerned, you could try populating the table to see if it still chooses a full table scan.
如果你问第一个查询如何使用像第二个查询那样的索引,我怀疑这是因为你的表中没有足够的行来证明两个索引查找的合理性。全表扫描的行计数为17行,因此它认为只需旋转行寻找两个ID就行得更快。如果您担心,可以尝试填充表格以查看它是否仍然选择全表扫描。
#1
If you are asking how to make the first query use an index like the second query does, I suspect it is because there aren't enough rows in your table to justify two index lookups. The row count for a full table scan is 17 rows, so it thinks it is faster just to spin across the rows looking for two ids. If you are concerned, you could try populating the table to see if it still chooses a full table scan.
如果你问第一个查询如何使用像第二个查询那样的索引,我怀疑这是因为你的表中没有足够的行来证明两个索引查找的合理性。全表扫描的行计数为17行,因此它认为只需旋转行寻找两个ID就行得更快。如果您担心,可以尝试填充表格以查看它是否仍然选择全表扫描。