还是上次那个问题（ACMAIN_CHM wwwwA, wwwwb等请进）

http://topic.csdn.net/u/20090520/16/a96a2e90-a935-4460-837e-e52b4557c519.html
这个帖子中我一直强调的大数据量和小数据量的问题其实是个误导！
并不是因为数据量，而是某个索引没有起作用，导致了全表扫描！应该是这样，我不是百分百确定！

现在，删除了几个不必要的索引，把问题集中锁定在如下语句：
EXPLAIN SELECT SQL_NO_CACHE E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 1;
+----+-------------+-------------+-------+---------------+---------+---------+-------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------------------------+---------+-------------+
| 1 | SIMPLE | E_info | index | PRIMARY | PRIMARY | 4 | NULL | 7300143 | |
| 1 | SIMPLE | E_role_info | ref | infoID | infoID | 4 | news_data.E_info.infoID | 7 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+-------------------------+---------+-------------+
E_info.infoID是主键,而且上边E_info的type项已经是index了，但不清楚为什么rows项的数值还是那么大，7300143 这个数字也恰恰是E_info的行数！
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM E_info;
+----------+
| COUNT(*) |
+----------+
| 7300143 |
+----------+


所用的存储引擎是MyISAM,索引文件E_info.MYI是100M大小，我怀疑是索引太大，内存撑不下，老得访问磁盘所致，故而将key_buffer_size增加到512，可依然很慢，查第一条记录要18秒。
mysql>SELECT SQL_NO_CACHE E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 1;
+----------+---------------------+--------+---------+
| infoID | info_time | roleID | related |
+----------+---------------------+--------+---------+
| 42570652 | 2009-04-14 13:27:45 | 25669 | 1001 |
+----------+---------------------+--------+---------+
1 row in set (18.89 sec)



再描述一下E_role_info表，
mysql> show columns from E_role_info;
+---------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| infoID | int(8) unsigned | NO | MUL | 0 | |
| roleID | mediumint(6) unsigned | NO | | 0 | |
| related | smallint(3) unsigned | NO | | 0 | |
+---------+-----------------------+------+-----+---------+-------+
我在该表中插入一条记录
INSERT INTO E_role_info(infoID, roleID, related) values(42880361, 25669, 1);

这时很快就列出了刚刚记录的结果：
mysql> SELECT SQL_NO_CACHE E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 1;
+----------+---------------------+--------+---------+
| infoID | info_time | roleID | related |
+----------+---------------------+--------+---------+
| 42880361 | 2009-04-18 08:58:28 | 25669 | 1 |
+----------+---------------------+--------+---------+
1 row in set (0.00 sec)

但，如果列出两条的话，就会由于以前数据的拖累，而很慢：
SELECT SQL_NO_CACHE E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 2;
+----------+---------------------+--------+---------+
| infoID | info_time | roleID | related |
+----------+---------------------+--------+---------+
| 42880361 | 2009-04-18 08:58:28 | 25669 | 1 |
| 42570652 | 2009-04-14 13:27:45 | 25669 | 1001 |
+----------+---------------------+--------+---------+
2 rows in set (17.78 sec)



通过结果可以看出，两次列出的值中42880361比42570652要新！

所以，我估计是由于某种原因E_info的主键没有用上，即便explain的type值显示的是index.
extra中没有using filesort说明应该不是ORDER BY的问题，很可能是JOIN阶段的问题:
`E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID
E_info.infoID是本表的主键，E_role_info.infoID也建立了索引！