在mysql 4.1.20下
执行一个主从表的嵌套查询时,发现不能主sql不能引用到索引导致查询速度非常慢,哪位知道是什么问题?
下面是执行计划
mysql> explain select * from QDETAIL where QID in (select QID from QINFO where ID='1234567890');
+----+--------------------+-----------+-----------------+-----------------+---------+---------+------+--------+--------------------------+
| id | select_type        | table     | type            | possible_keys   | key     | key_len | ref  | rows   | Extra                    |
+----+--------------------+-----------+-----------------+-----------------+---------+---------+------+--------+--------------------------+
|  1 | PRIMARY            | QDETAIL | ALL             | NULL            | NULL    |    NULL | NULL | 173488 | Using where              |
|  2 | DEPENDENT SUBQUERY | QINFO     | unique_subquery | PRIMARY,IDX_QID | PRIMARY |       4 | func |      1 | Using index; Using where |
+----+--------------------+-----------+-----------------+-----------------+---------+---------+------+--------+--------------------------+
2 rows in set (0.00 sec)

而切分这两个sql单独执行则都能应用到索引.
mysql> explain select QID from QINFO where ID='1234567890';
+----+-------------+-------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | QINFO | ref  | IDX_ID     | IDX_ID |      45 | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)1 row in set (0.00 sec)

mysql> explain select * from QDETAIL where QID in(11111,222,3333);
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | QDETAIL | range | QID           | QID  |       4 | NULL |    3 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

[ 本帖最后由 msdn_lib 于 2008-5-6 10:20 编辑 ]

exolain select QDETAIL.* from QDETAIL inner join QINFO  on  QDETAIL.QID = QINFO.QID  where QINFO.ID='1234567890';

你这样试下

IN 是没有办法用到索引的。

楼上有没有官方手册手册关于in索引问题的讨论章节?