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

Question

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也建立了索引！

Answer 1

后来在E_info.infoID主键上又建立了个索引，并强制该索引，还是无效！CREATE INDEX idx_e_info_id ON E_info(infoID);mysql> EXPLAIN SELECT SQL_NO_CACHE E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` FORCE INDEX(idx_e_info_id) 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;
+----+-------------+-------------+-------+---------------+---------------+---------+-------------------------+---------+-------------+
| id | select_type | table       | type  | possible_keys | key           | key_len | ref                     | rows    | Extra       |
+----+-------------+-------------+-------+---------------+---------------+---------+-------------------------+---------+-------------+
|  1 | SIMPLE      | E_info      | index | idx_e_info_id | idx_e_info_id | 4       | NULL                    | 7300143 |             | 
|  1 | SIMPLE      | E_role_info | ref   | infoID        | infoID        | 4       | news_data.E_info.infoID |       7 | Using where | 
+----+-------------+-------------+-------+---------------+---------------+---------+-------------------------+---------+-------------+
2 rows in set (0.03 sec)mysql> SELECT SQL_NO_CACHE E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` FORCE INDEX(idx_e_info_id) 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 (25.73 sec)

Answer 2

提供一下以下信息。show table status like 'E_role_info'; 
show table status like 'E_info'; show index from E_role_info;
show index from E_info;select count(*) from E_role_info where roleID = 25669 ;

Answer 3

\G格式的：mysql> show table status like 'E_role_info'\G 
*************************** 1. row ***************************
           Name: E_role_info
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 72997534
 Avg_row_length: 10
    Data_length: 729975340
Max_data_length: 2814749767106559
   Index_length: 745362432
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2009-05-26 14:12:43
    Update_time: 2009-05-26 17:21:00
     Check_time: 2009-05-26 14:19:33
      Collation: gb2312_chinese_ci
       Checksum: NULL
 Create_options: 
        Comment: ????????mysql> show table status like 'E_info'\G
*************************** 1. row ***************************
           Name: E_info
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 7300143
 Avg_row_length: 130
    Data_length: 951029956
Max_data_length: 281474976710655
   Index_length: 179054592
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2009-05-26 18:04:41
    Update_time: 2009-05-26 18:08:11
     Check_time: 2009-05-26 18:09:31
      Collation: gb2312_chinese_ci
       Checksum: NULL
 Create_options: 
        Comment: ???????
mysql> show index from E_role_info\G
*************************** 1. row ***************************
       Table: E_role_info
  Non_unique: 1
    Key_name: infoID
Seq_in_index: 1
 Column_name: infoID
   Collation: A
 Cardinality: 10428219
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: mysql> show index from E_info\G
*************************** 1. row ***************************
       Table: E_info
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: infoID
   Collation: A
 Cardinality: 7300143
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
mysql> select count(*) from E_role_info where roleID = 25669 ;
+----------+
| count(*) |
+----------+
|       30 | 
+----------+
1 row in set (30.05 sec)

Answer 4

mysql> show table status like 'E_role_info'; 
+-------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+----------+
| Name        | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment  |
+-------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+----------+
| E_role_info | MyISAM |      10 | Fixed      | 72997534 |             10 |   729975340 | 2814749767106559 |    745362432 |         0 |           NULL | 2009-05-26 14:12:43 | 2009-05-26 17:21:00 | 2009-05-26 14:19:33 | gb2312_chinese_ci |     NULL |                | ???????? | 
+-------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+----------+
mysql> show table status like 'E_info'; 
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| E_info | MyISAM |      10 | Dynamic    | 7300143 |            130 |   951029956 | 281474976710655 |    104653824 |         0 |           NULL | 2009-05-27 09:06:05 | 2009-05-27 09:09:36 | NULL       | gb2312_chinese_ci |     NULL |                | ??????? | 
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
mysql> show index from E_role_info; 
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_role_info |          1 | infoID   |            1 | infoID      | A         |    10428219 |     NULL | NULL   |      | BTREE      |         | 
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
mysql> show index from E_info; 
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_info |          0 | PRIMARY  |            1 | infoID      | A         |     7300143 |     NULL | NULL   |      | BTREE      |         | 
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+mysql> select count(*) from E_role_info where roleID = 25669 ; 
+----------+ 
| count(*) | 
+----------+ 
|      30 | 
+----------+ 
1 row in set (30.05 sec) 

Answer 5

呵呵，这两天换机器，N多数据，没有上论坛
你将LEFT　JOIN修改为INNER　JOIN是否可行

Answer 6

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 = 25669orSELECT 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 and E_role_info.roleID = 25669这两条SQL语句执行速度如何

Answer 7

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
慢
EXPLAIN结果：
+----+-------------+-------------+------+---------------+--------+---------+-------------------------+---------+-------------+
| id | select_type | table       | type | possible_keys | key    | key_len | ref                     | rows    | Extra       |
+----+-------------+-------------+------+---------------+--------+---------+-------------------------+---------+-------------+
|  1 | SIMPLE      | E_info      | ALL  | PRIMARY       | NULL   | NULL    | NULL                    | 7300143 |             | 
|  1 | SIMPLE      | E_role_info | ref  | infoID        | infoID | 4       | news_data.E_info.infoID |       7 | Using where | 
+----+-------------+-------------+------+---------------+--------+---------+-------------------------+---------+-------------+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 and E_role_info.roleID = 25669;
也不行：
explain结果：
+----+-------------+-------------+------+---------------+--------+---------+-------------------------+---------+-------------+
| id | select_type | table       | type | possible_keys | key    | key_len | ref                     | rows    | Extra       |
+----+-------------+-------------+------+---------------+--------+---------+-------------------------+---------+-------------+
|  1 | SIMPLE      | E_info      | ALL  | PRIMARY       | NULL   | NULL    | NULL                    | 7300143 |             | 
|  1 | SIMPLE      | E_role_info | ref  | infoID        | infoID | 4       | news_data.E_info.infoID |       7 | Using where | 
+----+-------------+-------------+------+---------------+--------+---------+-------------------------+---------+-------------+

Answer 8

两条语句都耗时在sending data上！

Answer 9

不用转innodb了，一是优化基本上和存储引擎无关，二是innodb是读的速度上平均是弱于MyISAM的。三 MySQL在对有值的inner join,left join没什么大的差别。能不能不要用\G 然后把你的代码标为SQL代码，这样容易对比分析。

Answer 10

指的是这些吗？mysql> show table status like 'E_role_info'; 
+-------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+----------+ 
| Name        | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time        | Update_time        | Check_time          | Collation        | Checksum | Create_options | Comment  | 
+-------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+----------+ 
| E_role_info | MyISAM |      10 | Fixed      | 72997534 |            10 |  729975340 | 2814749767106559 |    745362432 |        0 |          NULL | 2009-05-26 14:12:43 | 2009-05-26 17:21:00 | 2009-05-26 14:19:33 | gb2312_chinese_ci |    NULL |                | ???????? | 
+-------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+----------+ 
mysql> show table status like 'E_info'; 
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 
| Name  | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time        | Update_time        | Check_time | Collation        | Checksum | Create_options | Comment | 
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 
| E_info | MyISAM |      10 | Dynamic    | 7300143 |            130 |  951029956 | 281474976710655 |    104653824 |        0 |          NULL | 2009-05-27 09:06:05 | 2009-05-27 09:09:36 | NULL      | gb2312_chinese_ci |    NULL |                | ??????? | 
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 
mysql> show index from E_role_info; 
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
| E_role_info |          1 | infoID  |            1 | infoID      | A        |    10428219 |    NULL | NULL  |      | BTREE      |        | 
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
1 row in set (0.00 sec) 
mysql> show index from E_info; 
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
| E_info |          0 | PRIMARY  |            1 | infoID      | A        |    7300143 |    NULL | NULL  |      | BTREE      |        | 
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
mysql> select count(*) from E_role_info where roleID = 25669 ; 
+----------+ 
| count(*) | 
+----------+ 
|      30 | 
+----------+ 
1 row in set (30.05 sec) 

Answer 11

问题的确比较怪异。从你一楼的explain 上MySQL　使用了E_info  的 PRIMARY 索引但行数异常。我在本机做了测试，一切正常。建议做如下操作
analyze  table E_role_info ; analyze table E_info 
另外你的表结构是不是如下。 E_role_info
+----------+--------+---------+
| infoID   | roleID | related |
+----------+--------+---------+
| 42245717 | 413382 |  1004 | 
| 42245750 | 413382 |  1006 | 
| 42245774 | 413382 |  1006 | E_info
+----------+---------------------------------------+---------------------+
| infoID   | info_title                            | info_time           |
+----------+---------------------------------------+---------------------+
| 42245717 | 南昌工商巧助企业护“英雄”              | 2009-04-09 12:33:43 | 
| 42245774 | 4月12日北京龙翔永恒台球俱乐部千元周赛 | 2009-04-09 11:36:36 | 
+----------+---------------------------------------+---------------------+

Answer 12

很不好意思，今天在想模拟你的问题的时候才理解了你的表结构。infoID 在你的两个表中都是主键？ 我建表如下mysql> desc E_role_info;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| infoID  | int(11) | NO   | PRI | NULL    |       |
| roleID  | int(11) | NO   | MUL | NULL    |       |
| related | int(11) | NO   |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> desc E_info;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| infoID     | int(11)      | NO   | PRI | NULL    |       |
| info_title | varchar(100) | NO   |     | NULL    |       |
| info_time  | datetime     | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> show index from E_role_info;
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_role_info |          0 | PRIMARY                |            1 | infoID      | A         |          30 |     NULL | NULL   |      | BTREE      |         |
| E_role_info |          1 | idx_E_role_info_roleID |            1 | roleID      | A         |          10 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)mysql> show index from E_info;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_info |          0 | PRIMARY  |            1 | infoID      | A         |          30 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)mysql> explain
    -> select E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.r
elated
    -> FROM `E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID    -> WHERE E_role_info.roleID = 3
    -> ORDER BY E_info.infoID DESC LIMIT 1;
+----+-------------+-------------+--------+--------------------------------+------------------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys                  | key                    | key_len | ref                     | rows | Extra                                        |
+----+-------------+-------------+--------+--------------------------------+------------------------+---------+-------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | E_role_info | ref    | PRIMARY,idx_E_role_info_roleID | idx_E_role_info_roleID | 4       | const                   |    7 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | E_info      | eq_ref | PRIMARY                        | PRIMARY                | 4       | test.E_role_info.infoID |    1 |                                              |
+----+-------------+-------------+--------+--------------------------------+------------------------+---------+-------------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)mysql>

Answer 13

ACMAIN_CHM 先生真是太感谢了，要是早知道您那么麻烦在自己机子上测试，我就把我搭的环境告诉您了！（本来想告诉您帮忙测试的，但觉得会给您添麻烦，不太合适！）稍后我把公网上的测试机器弄好，便将ip等信息通过站内短信息方式告诉您！

Answer 14

如果JOIN都慢的话，索引、表是否有问题？

Answer 15

是的，我的表结构就是如此，infoID在E_info中是主键，在E_role_info中不是主键，但建立了索引！

Answer 16

mysql> show index from E_role_info;
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_role_info |          0 | PRIMARY                |            1 | infoID      | A         |          30 |     NULL | NULL   |      | BTREE      |         |
| E_role_info |          1 | idx_E_role_info_roleID |            1 | roleID      | A         |          10 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)E_role_info 这个表中的infoID不是主键，但有索引，infoID和roleID在该表中是多对多的关系！

Answer 17

infoID和roleID在该表中是多对多的关系！也就是说这个表中主键应该是 (infoID,roleID)
数据类似+--------+--------+---------+
| infoID | roleID | related |
+--------+--------+---------+
|      2 |      1 |      20 |
|      2 |      3 |      20 |
|      2 |      4 |      20 |
|      3 |      2 |      30 |
|      3 |      5 |      30 |
|      4 |      1 |      40 |
|      4 |      2 |      40 |
|      4 |      4 |      40 |
|      4 |      5 |      40 |
|      5 |      5 |      50 |

Answer 18

是的，主键是(infoID,roleID)！不过我没有建立！

Answer 19

我现在建立了E_role_info(infoID,roleID)的主键，
速度倒是提高了一倍，但是还是慢，并没解决主要问题：
explain:+----+-------------+------------------+--------+---------------+---------+---------+-------------------------------+---------+-------+
| 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      | tmp_E_role_info1 | eq_ref | PRIMARY       | PRIMARY | 7       | news_data.E_info.infoID,const |       1 |       | 
+----+-------------+------------------+--------+---------------+---------+---------+-------------------------------+---------+-------+

Answer 20

建议你把主键加上，否则在做JOIN的时候会无索引可利用。mysql> desc E_role_info;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| infoID  | int(11) | NO   | PRI | NULL    |       |
| roleID  | int(11) | NO   | PRI | NULL    |       |
| related | int(11) | NO   |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> desc E_info;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| infoID     | int(11)      | NO   | PRI | NULL    |       |
| info_title | varchar(100) | NO   |     | NULL    |       |
| info_time  | datetime     | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> show index from E_role_info;
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_role_info |          0 | PRIMARY                |            1 | infoID      | A         |         119 |     NULL | NULL   |      | BTREE      |         |
| E_role_info |          0 | PRIMARY                |            2 | roleID      | A         |         119 |     NULL | NULL   |      | BTREE      |         |
| E_role_info |          1 | idx_E_role_info_roleID |            1 | roleID      | A         |          10 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)mysql> show index from E_info;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_info |          0 | PRIMARY  |            1 | infoID      | A         |          30 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)mysql> select count(*) from E_role_info where roleID = 3 ;
+----------+
| count(*) |
+----------+
|       25 |
+----------+
1 row in set (0.00 sec)mysql> select 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 = 3
    -> ORDER BY E_info.infoID DESC LIMIT 3;
+--------+---------------------+--------+---------+
| infoID | info_time           | roleID | related |
+--------+---------------------+--------+---------+
|     30 | 2009-05-27 12:44:27 |      3 |     274 |
|     29 | 2009-05-27 12:43:52 |      3 |     225 |
|     28 | 2009-05-27 12:35:13 |      3 |     259 |
+--------+---------------------+--------+---------+
3 rows in set (0.00 sec)mysql> explain
    -> select 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 = 3
    -> ORDER BY E_info.infoID DESC LIMIT 3;
+----+-------------+-------------+--------+--------------------------------+---------+---------+--------------------------+------+-------------+
| id | select_type | table       | type   | possible_keys                  | key     | key_len | ref                      | rows | Extra       |
+----+-------------+-------------+--------+--------------------------------+---------+---------+--------------------------+------+-------------+
|  1 | SIMPLE      | E_info      | index  | PRIMARY                        | PRIMARY | 4       | NULL                     |    3 |             |
|  1 | SIMPLE      | E_role_info | eq_ref | PRIMARY,idx_E_role_info_roleID | PRIMARY | 8       | test.E_info.infoID,const |    1 | Using where |
+----+-------------+-------------+--------+--------------------------------+---------+---------+--------------------------+------+-------------+
2 rows in set (0.00 sec)mysql>

Answer 21

加上了，E_role_info(infoID,roleID)的主键， 
速度倒是提高了一倍，但是还是慢，并没解决主要问题：
+----+-------------+------------------+--------+---------------+---------+---------+-------------------------------+---------+-------+
| 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      | tmp_E_role_info1 | eq_ref | PRIMARY       | PRIMARY | 7       | news_data.E_info.infoID,const |       1 |       | 
+----+-------------+------------------+--------+---------------+---------+---------+-------------------------------+---------+-------
E_info那一行的rows影响的行数还是那么大！

Answer 22

tmp_E_role_info1这个表是我单提列出来的只有30行的小表：+----------+--------+---------+
| infoID   | roleID | related |
+----------+--------+---------+
| 30988323 |  25669 |    1002 | 
| 31294141 |  25669 |    1050 | 
| 31335712 |  25669 |    1049 | 
| 31466752 |  25669 |    1002 | 
| 31788492 |  25669 |    1017 | 
| 31789503 |  25669 |    1011 | 
| 31797856 |  25669 |    1003 | 
| 31902436 |  25669 |    1006 | 
| 31906371 |  25669 |    1013 | 
| 32095002 |  25669 |    1003 | 
| 32575477 |  25669 |    1002 | 
| 32661765 |  25669 |    1002 | 
| 32924774 |  25669 |    1002 | 
| 33023668 |  25669 |    1003 | 
| 33097422 |  25669 |    1004 | 
| 33123314 |  25669 |    1017 | 
| 33206129 |  25669 |    1002 | 
| 33207634 |  25669 |    1002 | 
| 33629198 |  25669 |    1002 | 
| 33803833 |  25669 |    1002 | 
| 34014787 |  25669 |    1004 | 
| 34660663 |  25669 |    1003 | 
| 35942322 |  25669 |    1007 | 
| 38720505 |  25669 |    1002 | 
| 39723719 |  25669 |    1001 | 
| 40621938 |  25669 |    1008 | 
| 41689393 |  25669 |    1001 | 
| 41944750 |  25669 |    1007 | 
| 42570652 |  25669 |    1001 | 
| 42880361 |  25669 |       1 | 
+----------+--------+---------+
即便如此运行起来还是慢：mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, tmp_E_role_info1.roleID, tmp_E_role_info1.related FROM `E_info` INNER JOIN tmp_E_role_info1 USING(infoID) WHERE tmp_E_role_info1.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 | 
+----------+---------------------+--------+---------+
再列一遍explain:mysql> explain SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, tmp_E_role_info1.roleID, tmp_E_role_info1.related FROM `E_info` INNER JOIN tmp_E_role_info1 USING(infoID) WHERE tmp_E_role_info1.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 2;
+----+-------------+------------------+--------+---------------+---------+---------+-------------------------------+---------+-------+
| 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      | tmp_E_role_info1 | eq_ref | PRIMARY       | PRIMARY | 7       | news_data.E_info.infoID,const |       1 |       | 
+----+-------------+------------------+--------+---------------+---------+---------+-------------------------------+---------+-------+

Answer 23

explain SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, tmp_E_role_info1.roleID, tmp_E_role_info1.related FROM `E_info` INNER JOIN tmp_E_role_info1 USING(infoID) WHERE tmp_E_role_info1.roleID = 25669贴结果

Answer 24

+----+-------------+------------------+--------+---------------+---------+---------+-------------------------------+---------+-------+
| id | select_type | table            | type   | possible_keys | key     | key_len | ref                           | rows    | Extra |
+----+-------------+------------------+--------+---------------+---------+---------+-------------------------------+---------+-------+
|  1 | SIMPLE      | E_info           | ALL    | PRIMARY       | NULL    | NULL    | NULL                          | 7300143 |       | 
|  1 | SIMPLE      | tmp_E_role_info1 | eq_ref | PRIMARY       | PRIMARY | 7       | news_data.E_info.infoID,const |       1 |       | 
+----+-------------+------------------+--------+---------------+---------+---------+-------------------------------+---------+-------+

Answer 25

运行时间为53秒多：
mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, tmp_E_role_info1.roleID, tmp_E_role_info1.related FROM `E_info` INNER JOIN tmp_E_role_info1 USING(infoID) WHERE tmp_E_role_info1.roleID = 25669;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 35942322 | 2008-12-15 16:10:12 |  25669 |    1007 | 
| 38720505 | 2009-02-13 17:19:20 |  25669 |    1002 | 
| 39723719 | 2009-03-03 06:56:16 |  25669 |    1001 | 
| 40621938 | 2009-03-17 19:46:21 |  25669 |    1008 | 
| 41689393 | 2009-03-31 18:50:38 |  25669 |    1001 | 
| 41944750 | 2009-04-03 20:22:06 |  25669 |    1007 | 
| 42570652 | 2009-04-14 13:27:45 |  25669 |    1001 | 
| 42880361 | 2009-04-18 08:58:28 |  25669 |       1 | 
+----------+---------------------+--------+---------+
8 rows in set (53.60 sec)

Answer 26

再去掉where子句，结果如下：
mysql> EXPLAIN SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, tmp_E_role_info1.roleID, tmp_E_role_info1.related FROM `E_info` INNER JOIN tmp_E_role_info1 USING(infoID);                                        
+----+-------------+------------------+------+---------------+---------+---------+-------------------------+---------+-------+
| id | select_type | table            | type | possible_keys | key     | key_len | ref                     | rows    | Extra |
+----+-------------+------------------+------+---------------+---------+---------+-------------------------+---------+-------+
|  1 | SIMPLE      | E_info           | ALL  | PRIMARY       | NULL    | NULL    | NULL                    | 7300143 |       | 
|  1 | SIMPLE      | tmp_E_role_info1 | ref  | PRIMARY       | PRIMARY | 4       | news_data.E_info.infoID |       1 |       | 
+----+-------------+------------------+------+---------------+---------+---------+-------------------------+---------+-------+

Answer 27

建索引
create index idx_E_role_info_roleID on E_role_info(roleID,infoID);然后用下面语句可以强制MySQL先做 E_role_info.roleID = 3 的筛选，然后再做连接mysql> desc E_role_info;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| infoID  | int(11) | NO   | PRI | NULL    |       |
| roleID  | int(11) | NO   | PRI | NULL    |       |
| related | int(11) | NO   |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> desc E_info;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| infoID     | int(11)      | NO   | PRI | NULL    |       |
| info_title | varchar(100) | NO   |     | NULL    |       |
| info_time  | datetime     | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.03 sec)mysql> show index from E_role_info;
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_role_info |          0 | PRIMARY                |            1 |      infoID | A         |          59 |     NULL | NULL   |      | BTREE      |         |
| E_role_info |          0 | PRIMARY                |            2 |      roleID | A         |         119 |     NULL | NULL   |      | BTREE      |         |
| E_role_info |          1 | idx_E_role_info_roleID |            1 |      roleID | A         |          10 |     NULL | NULL   |      | BTREE      |         |
| E_role_info |          1 | idx_E_role_info_roleID |            2 |      infoID | A         |         119 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.01 sec)mysql> show index from E_info;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_info |          0 | PRIMARY  |            1 | infoID      | A         |          30 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)mysql> explain select STRAIGHT_JOIN  E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related
    -> FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID
    -> WHERE E_role_info.roleID = 3
    -> ORDER BY E_info.infoID DESC LIMIT 3;
+----+-------------+-------------+--------+------------------------+------------------------+---------+-------------------------+------+---------------------------------+
| id | select_type | table       | type   | possible_keys          | key                    | key_len | ref                     | rows | Extra                           |
+----+-------------+-------------+--------+------------------------+------------------------+---------+-------------------------+------+---------------------------------+
|  1 | SIMPLE      | E_role_info | ref    | idx_E_role_info_roleID | idx_E_role_info_roleID | 4       | const                   |   25 | Using temporary; Usingfilesort  |
|  1 | SIMPLE      | E_info      | eq_ref | PRIMARY                | PRIMARY                | 4       | test.E_role_info.infoID |    1 |                                 |
+----+-------------+-------------+--------+------------------------+------------------------+---------+-------------------------+------+---------------------------------+
2 rows in set (0.00 sec)mysql>

Answer 28

临时解决方案。
mysql> select b.infoID,b.info_time,a.roleID,a.related
    -> from (
    -> select roleID,related,infoID
    -> from E_role_info
    -> where roleID=25669) a inner join E_info b on a.infoID=b.infoID
    -> order by b.info_time desc limit 10;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42570652 | 2009-04-14 13:27:45 |  25669 |    1001 |
| 41944750 | 2009-04-03 20:22:06 |  25669 |    1007 |
| 41689393 | 2009-03-31 18:50:38 |  25669 |    1001 |
| 40621938 | 2009-03-17 19:46:21 |  25669 |    1008 |
| 39723719 | 2009-03-03 06:56:16 |  25669 |    1001 |
| 38720505 | 2009-02-13 17:19:20 |  25669 |    1002 |
| 35942322 | 2008-12-15 16:10:12 |  25669 |    1007 |
| 34660663 | 2008-11-20 21:19:51 |  25669 |    1003 |
| 34014787 | 2008-11-08 14:09:59 |  25669 |    1004 |
| 33803833 | 2008-11-04 21:31:38 |  25669 |    1002 |
+----------+---------------------+--------+---------+
10 rows in set (0.05 sec)mysql>

Answer 29

两种方法差不多，都是强制先做 WHERE E_role_info.roleID = 25669 ，但前提是这个结果集不能太大，如果 WHERE E_role_info.roleID = 25669 结果上万则会出现上次贴子中出现的问题。mysql> explain
    -> select b.infoID,b.info_time,a.roleID,a.related
    -> from (
    -> select roleID,related,infoID
    -> from E_role_info
    -> where roleID=25669) a inner join E_info b on a.infoID=b.infoID
    -> order by b.info_time desc limit 10;
+----+-------------+-------------+--------+---------------+---------+---------+----------+------+---------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len |ref       | rows | Extra                           |
+----+-------------+-------------+--------+---------------+---------+---------+----------+------+---------------------------------+
|  1 | PRIMARY     | <derived2>  | ALL    | NULL          | NULL    | NULL    |NULL      |   45 | Using temporary; Using filesort |
|  1 | PRIMARY     | b           | eq_ref | PRIMARY       | PRIMARY | 4       |a.infoID  |    1 |                                 |
|  2 | DERIVED     | E_role_info | ref    | roleID        | roleID  | 3       |          |   55 |                                 |
+----+-------------+-------------+--------+---------------+---------+---------+----------+------+---------------------------------+
3 rows in set (0.06 sec)mysql>
mysql> explain
    -> select STRAIGHT_JOIN  E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related
    -> FROM E_role_info FORCE INDEX( roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID
    -> WHERE E_role_info.roleID = 25669
    -> ORDER BY E_info.infoID DESC LIMIT 3;
+----+-------------+-------------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len |ref                            | rows | Extra                           |
+----+-------------+-------------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+
|  1 | SIMPLE      | E_role_info | ref    | roleID        | roleID  | 3       |const                          |   55 | Using temporary; Using filesort |
|  1 | SIMPLE      | E_info      | eq_ref | PRIMARY       | PRIMARY | 4       |news_data2.E_role_info.infoID  |    1 |                                 |
+----+-------------+-------------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+
2 rows in set (0.05 sec)mysql> select STRAIGHT_JOIN  E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related
    -> FROM E_role_info FORCE INDEX( roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID
    -> WHERE E_role_info.roleID = 25669
    -> ORDER BY E_info.infoID DESC LIMIT 3;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42570652 | 2009-04-14 13:27:45 |  25669 |    1001 |
| 41944750 | 2009-04-03 20:22:06 |  25669 |    1007 |
| 41689393 | 2009-03-31 18:50:38 |  25669 |    1001 |
+----------+---------------------+--------+---------+
3 rows in set (0.05 sec)mysql>

Answer 30

是啊，关键就是在我的应用中无法确定 WHERE E_role_info.roleID = 25669 的结果集， WHERE E_role_info.roleID = 25669 的结果集是很小，在order by 阶段中即便用了filesort也没什么，但一旦遇上WHERE E_role_info.roleID = 413382这样的超大的结果集，就很麻烦了！mysql> EXPLAIN select b.infoID,b.info_time,a.roleID,a.related from (     select roleID,related,infoID     from E_role_info     where roleID=413382) a inner join E_info b on a.infoID=b.infoID     order by b.info_time desc limit 10;
+----+-------------+-------------+--------+---------------+---------+---------+----------+---------+---------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref      | rows    | Extra                           |
+----+-------------+-------------+--------+---------------+---------+---------+----------+---------+---------------------------------+
|  1 | PRIMARY     | <derived2>  | ALL    | NULL          | NULL    | NULL    | NULL     | 3089656 | Using temporary; Using filesort |
|  1 | PRIMARY     | b           | eq_ref | PRIMARY       | PRIMARY | 4       | a.infoID |       1 |                                 |
|  2 | DERIVED     | E_role_info | ref    | roleID        | roleID  | 3       |          | 2895079 |                                 |
+----+-------------+-------------+--------+---------------+---------+---------+----------+---------+---------------------------------+
3 rows in set (7.66 sec)mysql> select b.infoID,b.info_time,a.roleID,a.related from (     select roleID,related,infoID     from E_role_info     where roleID=413382) a inner join E_info b on a.infoID=b.infoID     order by b.info_time desc limit 10;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42880323 | 2009-04-18 08:58:56 | 413382 |    1004 |
| 42880311 | 2009-04-18 08:58:24 | 413382 |    1006 |
| 42880353 | 2009-04-18 08:58:21 | 413382 |    1004 |
| 42880348 | 2009-04-18 08:58:21 | 413382 |    1006 |
| 42880347 | 2009-04-18 08:58:21 | 413382 |    1004 |
| 42880351 | 2009-04-18 08:58:20 | 413382 |    1006 |
| 42880340 | 2009-04-18 08:57:59 | 413382 |    1004 |
| 42880288 | 2009-04-18 08:57:45 | 413382 |    1004 |
| 42880314 | 2009-04-18 08:57:31 | 413382 |    1005 |
| 42880306 | 2009-04-18 08:57:31 | 413382 |    1005 |
+----------+---------------------+--------+---------+
10 rows in set (34.35 sec)

Answer 31

我现将order by中的E_info.info_time改为E_info.infoID这个逐渐来排序，因为join阶段也是用infoID，我想这样可以统一索引，使得join和order by都能达到很好的性能：
mysql> EXPLAIN SELECT STRAIGHT_JOIN 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 0 , 10;
+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref                      | rows     | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+
|  1 | SIMPLE      | E_info      | index | PRIMARY       | PRIMARY | 4       | NULL                     | 17868847 |             |
|  1 | SIMPLE      | E_role_info | ref   | infoID,roleID | infoID  | 4       | news_data2.E_info.infoID |        6 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+
2 rows in set (0.00 sec)这个看起来好像不错，type栏中一个是index一个是ref,但是E_info的rows的值太大了！实际运行起来确实也慢：mysql> SELECT STRAIGHT_JOIN 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 0 , 10;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42570652 | 2009-04-14 13:27:45 |  25669 |    1001 |
| 41944750 | 2009-04-03 20:22:06 |  25669 |    1007 |
| 41689393 | 2009-03-31 18:50:38 |  25669 |    1001 |
| 40621938 | 2009-03-17 19:46:21 |  25669 |    1008 |
| 39723719 | 2009-03-03 06:56:16 |  25669 |    1001 |
| 38720505 | 2009-02-13 17:19:20 |  25669 |    1002 |
| 35942322 | 2008-12-15 16:10:12 |  25669 |    1007 |
| 34660663 | 2008-11-20 21:19:51 |  25669 |    1003 |
| 34014787 | 2008-11-08 14:09:59 |  25669 |    1004 |
| 33803833 | 2008-11-04 21:31:38 |  25669 |    1002 |
+----------+---------------------+--------+---------+
10 rows in set (2 min 51.09 sec)我想，是不是E_info的主键infoID在join阶段没起到作用？

Answer 32

mysql> select count(*) from E_role_info where roleID=413382;
+----------+
| count(*) |
+----------+
|  3089656 |
+----------+
1 row in set (0.94 sec)mysql>
这种情况，换成人来决定，如何进行查询？
一种方法如下。
select b.infoID,b.info_time,a.roleID,a.related from ( select roleID,related,infoID from E_role_info where roleID=413382 order by infoID desc limit 10) a inner join E_info b on a.infoID=b.infoID order by b.info_
这样通过 select roleID,related,infoID from E_role_info where roleID=413382 order by infoID desc limit 10 只返回10条，速度应该可以提高。
但不幸的是，你的索引不对, 为了加快这个查询  where roleID=413382 order by infoID  需要一个 index (roleID,infoID)的索引mysql> show index from E_role_info;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_role_info |          1 | infoID   |            1 | infoID      | A         |    13850305 |     NULL | NULL   |      | BTREE      |         |
| E_role_info |          1 | roleID   |            1 | roleID      | A         |       71639 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.05 sec)

Answer 33

这个确实挺快的，已经开始应用，呵呵！谢谢您了！不过，改为order by infoID 只是中间的权宜之计，最后还得改回order by info_time.
之所以目前改为order by infoID，是想看看join和order by统一了针对一个索引之后，是否能够很快。
可是还是不快！
mysql> EXPLAIN SELECT STRAIGHT_JOIN 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 0 , 10;
+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref                      | rows     | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+
|  1 | SIMPLE      | E_info      | index | PRIMARY       | PRIMARY | 4       | NULL                     | 17868847 |             |
|  1 | SIMPLE      | E_role_info | ref   | infoID,roleID | infoID  | 4       | news_data2.E_info.infoID |        6 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+
2 rows in set (0.00 sec)mysql> SELECT STRAIGHT_JOIN 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 0 , 10;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42570652 | 2009-04-14 13:27:45 |  25669 |    1001 |
| 41944750 | 2009-04-03 20:22:06 |  25669 |    1007 |
| 41689393 | 2009-03-31 18:50:38 |  25669 |    1001 |
| 40621938 | 2009-03-17 19:46:21 |  25669 |    1008 |
| 39723719 | 2009-03-03 06:56:16 |  25669 |    1001 |
| 38720505 | 2009-02-13 17:19:20 |  25669 |    1002 |
| 35942322 | 2008-12-15 16:10:12 |  25669 |    1007 |
| 34660663 | 2008-11-20 21:19:51 |  25669 |    1003 |
| 34014787 | 2008-11-08 14:09:59 |  25669 |    1004 |
| 33803833 | 2008-11-04 21:31:38 |  25669 |    1002 |
+----------+---------------------+--------+---------+
10 rows in set (2 min 51.09 sec)很疑惑的是，在这个问题中，即便infoID是主键，性能还是那么糟糕，而且在explain type那一栏显示了是index。
我现在很想知道，在这个sql语句中，主键没起作用吗？我想先解决order by infoID在这个语句中索引失效的问题，然后再慢慢解决order by info_time的问题！不知道思路对不对！

Answer 34

是的，这个主键很难有什么作用，E_info(infoID) 是主键，但即使由你现在人工来决定，这个主键也没什么大用啊。只是在JOIN的时候会用上。比如现在一切由你人工决定：
问题１：你是先做 `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID  ,还是先做  WHERE E_role_info.roleID = 25669 ?
问题２：如果先做 WHERE E_role_info.roleID = 25669 这时候你会选择哪个索引　？　E_role_info(roleID)？
问题３：where 之后的结果集你已经有了。现在做 JOIN ，此时有哪些索引可以用？ E_info (infoID) 主键。
总是４：JOIN完后，order by info_time 现在有什么索引可用吗？  无

Answer 35

如果是我考虑这些问题
问题１：你是先做 `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID  ,还是先做  WHERE E_role_info.roleID = 25669 ? 
   答：应该先做WHERE E_role_info.roleID = 25669 这样可以在join阶段过滤大量没有必要的结果集！
问题２：如果先做 WHERE E_role_info.roleID = 25669 这时候你会选择哪个索引　？　E_role_info(roleID)？
   答：没错，就我粗浅的水平来说，我会选择E_role_info(roleID)
问题３：where 之后的结果集你已经有了。现在做 JOIN ，此时有哪些索引可以用？ E_info (infoID) 主键。 
   答：我认为，join会用到E_info(infoID)的索引，理由是，where的结果出来后，会遍历这个E_role_info.roleID = 25669 的结果集，遍历的过程中会针对每个E_role_info(infoID)去找到相应的E_info(infoID),这样的话应该是选择E_info(infoID)上的索引，这样会比较快！
总是４：JOIN完后，order by info_time 现在有什么索引可用吗？  无
   答：是的，我也觉得无。可是现在为什么order by E_info.infoID的情况下E_info(infoID)也没有用呢？
这里我已经将ORDER BY info_time改为ORDER BY E_info.infoID，最后还是很慢！mysql> EXPLAIN SELECT STRAIGHT_JOIN 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 0 , 10;
+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref                      | rows     | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+
|  1 | SIMPLE      | E_info      | index | PRIMARY       | PRIMARY | 4       | NULL                     | 17868847 |             |
|  1 | SIMPLE      | E_role_info | ref   | infoID,roleID | infoID  | 4       | news_data2.E_info.infoID |        6 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+
2 rows in set (0.00 sec)

Answer 36

所以在这个sql语句中，我设想join阶段和order by E_info.infoID都会用到主键的索引，也就是E_info(infoID)上的索引！

Answer 37

下面是你目录的索引情况。
mysql> show index from E_info;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_info |          0 | PRIMARY   |            1 | infoID      | A         |17868847 |     NULL | NULL   |      | BTREE      |         |
| E_info |          1 | info_time |            1 | info_time   | A         | 8934423 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.05 sec)mysql> show index from E_role_info;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_role_info |          1 | infoID   |            1 | infoID      | A         |    13850305 |     NULL | NULL   |      | BTREE      |         |
| E_role_info |          1 | roleID   |            1 | roleID      | A         |       71639 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.03 sec)mysql>我们看一下MySQL会怎么执行？ SELECT STRAIGHT_JOIN 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 0 , 10;由于 E_role_info.roleID 的 Cardinality = 71639,  而你有记录 83101830 行, 所以MySQL会直接先进行  `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID|  1 | SIMPLE      | E_info      | index | PRIMARY       | PRIMARY | 4       | NULL                     | 17868847 |             |
此时会用到  E_info 的 PRIMARY  然后会用到 E_role_info.infoID
|  1 | SIMPLE      | E_role_info | ref   | infoID,roleID | infoID  | 4       | news_data2.E_info.infoID |        6 | Using where |和问题4相同。 JOIN完后，order by infoID 现在有什么索引可用吗？  无

Answer 38

呵呵，刚刚按照您说的建好了E_role_info(infoID, roleID)的主键（速度确实加快了不少）：
昨天晚上就开始弄，主要是E_role_info(infoID, roleID)含有大量的重复，还得过滤重复内容！下面我列一下新的测试结果（语句还是上边那个语句）：
mysql> show index from E_info;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_info |          0 | PRIMARY   |            1 | infoID      | A         |    17868847 |     NULL | NULL   |      | BTREE      |         |
| E_info |          1 | info_time |            1 | info_time   | A         |     8934423 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)mysql> show index from E_role_info;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| E_role_info |          0 | PRIMARY  |            1 | infoID      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| E_role_info |          0 | PRIMARY  |            2 | roleID      | A         |    82797994 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)mysql> EXPLAIN SELECT SQL_NO_CACHE STRAIGHT_JOIN 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 0 , 10;
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------------+----------+-------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                            | rows     | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------------+----------+-------+
|  1 | SIMPLE      | E_info      | index  | PRIMARY       | PRIMARY | 4       | NULL                           | 17868847 |       |
|  1 | SIMPLE      | E_role_info | eq_ref | PRIMARY       | PRIMARY | 7       | news_data2.E_info.infoID,const |        1 |       |
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------------+----------+-------+
2 rows in set (0.00 sec)mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN 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 0 , 10;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42570652 | 2009-04-14 13:27:45 |  25669 |       0 |
| 41944750 | 2009-04-03 20:22:06 |  25669 |       0 |
| 41689393 | 2009-03-31 18:50:38 |  25669 |       0 |
| 40621938 | 2009-03-17 19:46:21 |  25669 |       0 |
| 39723719 | 2009-03-03 06:56:16 |  25669 |       0 |
| 38720505 | 2009-02-13 17:19:20 |  25669 |       0 |
| 35942322 | 2008-12-15 16:10:12 |  25669 |       0 |
| 34660663 | 2008-11-20 21:19:51 |  25669 |       0 |
| 34014787 | 2008-11-08 14:09:59 |  25669 |       0 |
| 33803833 | 2008-11-04 21:31:38 |  25669 |       0 |
+----------+---------------------+--------+---------+
10 rows in set (1 min 14.45 sec)mysql> show profile;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| (initialization)               | 0.000002  |
| checking query cache for query | 0.0000900 |
| Opening tables                 | 0.000011  |
| System lock                    | 0.000005  |
| Table lock                     | 0.000008  |
| init                           | 0.000023  |
| optimizing                     | 0.000015  |
| statistics                     | 0.00002   |
| preparing                      | 0.000016  |
| executing                      | 0.000003  |
| Sorting result                 | 0.000003  |
| Sending data                   | 74.450095 |
| end                            | 0.000008  |
| query end                      | 0.000003  |
| freeing items                  | 0.00001   |
| closing tables                 | 0.000004  |
| logging slow query             | 0.000002  |
+--------------------------------+-----------+
17 rows in set (0.01 sec)

Answer 39

哦，原来会根据Cardinality 这个值来改变顺序啊！

Answer 40

不是E_role_info(infoID, roleID) 是 E_role_info(roleID ,infoID )

Answer 41

看到你的索引了。
mysql> show index from E_role_info;
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+-
| Table       | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | 
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+-
| E_role_info |          0 | PRIMARY                |            1 |      infoID | A         |        NULL | 
| E_role_info |          0 | PRIMARY                |            2 |      roleID | A         |    82797994 | 
| E_role_info |          1 | idx_E_role_info_roleID |            1 |      roleID | A         |       71624 | 
| E_role_info |          1 | idx_E_role_info_roleID |            2 |      infoID | A         |    82797994 | 
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+-
4 rows in set (0.05 sec)mysql> explain
    -> select roleID,related,infoID
    -> from E_role_info
    -> where roleID=413382
    -> order by infoID
    -> desc limit 10 ;
+----+-------------+-------------+------+------------------------+------------------------+---------+-------+---------+-------------+
| id | select_type | table       | type | possible_keys          | key                    | key_len | ref   | rows    | Extra       |
+----+-------------+-------------+------+------------------------+------------------------+---------+-------+---------+-------------+
|  1 | SIMPLE      | E_role_info | ref  | idx_E_role_info_roleID | idx_E_role_info_roleID | 3       | const | 2753512 | Using where |
+----+-------------+-------------+------+------------------------+------------------------+---------+-------+---------+-------------+
1 row in set (0.03 sec)mysql> select roleID,related,infoID
    -> from E_role_info
    -> where roleID=413382
    -> order by infoID
    -> desc limit 10 ;
+--------+---------+----------+
| roleID | related | infoID   |
+--------+---------+----------+
| 413382 |       0 | 42880359 |
| 413382 |       0 | 42880353 |
| 413382 |       0 | 42880351 |
| 413382 |       0 | 42880348 |
| 413382 |       0 | 42880347 |
| 413382 |       0 | 42880341 |
| 413382 |       0 | 42880340 |
| 413382 |       0 | 42880339 |
| 413382 |       0 | 42880338 |
| 413382 |       0 | 42880336 |
+--------+---------+----------+
10 rows in set (0.05 sec)mysql>这样，速度应该没问题了，但如果想解决 order by info_time  仍是不行。关键是要先能够人为的发现一条查找的路径。

Answer 42

mysql> explain SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info FORCE INDEX(idx_E_role_info_roleID) ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 0 , 10;
+----+-------------+-------------+-------+------------------------+------------------------+---------+--------------------------------+----------+-------+
| id | select_type | table       | type  | possible_keys          | key                    | key_len | ref                            | rows     | Extra |
+----+-------------+-------------+-------+------------------------+------------------------+---------+--------------------------------+----------+-------+
|  1 | SIMPLE      | E_info      | index | PRIMARY                | PRIMARY                | 4       | NULL                           | 17868847 |       |
|  1 | SIMPLE      | E_role_info | ref   | idx_E_role_info_roleID | idx_E_role_info_roleID | 7       | const,news_data2.E_info.infoID |        1 |       |
+----+-------------+-------------+-------+------------------------+------------------------+---------+--------------------------------+----------+-------+
2 rows in set (0.00 sec)mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info FORCE INDEX(idx_E_role_info_roleID) ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 0 , 10;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42570652 | 2009-04-14 13:27:45 |  25669 |       0 |
| 41944750 | 2009-04-03 20:22:06 |  25669 |       0 |
| 41689393 | 2009-03-31 18:50:38 |  25669 |       0 |
| 40621938 | 2009-03-17 19:46:21 |  25669 |       0 |
| 39723719 | 2009-03-03 06:56:16 |  25669 |       0 |
| 38720505 | 2009-02-13 17:19:20 |  25669 |       0 |
| 35942322 | 2008-12-15 16:10:12 |  25669 |       0 |
| 34660663 | 2008-11-20 21:19:51 |  25669 |       0 |
| 34014787 | 2008-11-08 14:09:59 |  25669 |       0 |
| 33803833 | 2008-11-04 21:31:38 |  25669 |       0 |
+----------+---------------------+--------+---------+
10 rows in set (1 min 13.66 sec)为什么我这还不行？在查询25669的时候？不过，这个时间倒是比以前的4分多钟快多了！确实是门学问啊，呵呵！

Answer 43

楼主啊，注意细节，你的语句和我37楼 的并不一样啊。mysql> explain
    -> select SQL_NO_CACHE STRAIGHT_JOIN
    ->  E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related
    -> FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID
    -> WHERE E_role_info.roleID = 25669
    -> ORDER BY E_info.infoID DESC LIMIT 10;
+----+-------------+-------------+--------+------------------------+------------------------+---------+-------------------------------+------+---
| id | select_type | table       | type   | possible_keys          | key                    | key_len | ref                           | rows | Ex
+----+-------------+-------------+--------+------------------------+------------------------+---------+-------------------------------+------+---
|  1 | SIMPLE      | E_role_info | ref    | idx_E_role_info_roleID | idx_E_role_info_roleID | 3       | const                         |   40 | Us
|  1 | SIMPLE      | E_info      | eq_ref | PRIMARY                | PRIMARY                | 4       | news_data2.E_role_info.infoID |    1 |   
+----+-------------+-------------+--------+------------------------+------------------------+---------+-------------------------------+------+---
2 rows in set (0.05 sec)mysql> select SQL_NO_CACHE STRAIGHT_JOIN
    ->  E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related
    -> FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID
    -> WHERE E_role_info.roleID = 25669
    -> ORDER BY E_info.infoID DESC LIMIT 10;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42570652 | 2009-04-14 13:27:45 |  25669 |       0 |
| 41944750 | 2009-04-03 20:22:06 |  25669 |       0 |
| 41689393 | 2009-03-31 18:50:38 |  25669 |       0 |
| 40621938 | 2009-03-17 19:46:21 |  25669 |       0 |
| 39723719 | 2009-03-03 06:56:16 |  25669 |       0 |
| 38720505 | 2009-02-13 17:19:20 |  25669 |       0 |
| 35942322 | 2008-12-15 16:10:12 |  25669 |       0 |
| 34660663 | 2008-11-20 21:19:51 |  25669 |       0 |
| 34014787 | 2008-11-08 14:09:59 |  25669 |       0 |
| 33803833 | 2008-11-04 21:31:38 |  25669 |       0 |
+----------+---------------------+--------+---------+
10 rows in set (1.00 sec)mysql>

Answer 44

知道了，ACMAIN_CHM先生一直在让我缩小join的范围，并提高，从而加快速度！
所以，您一直在考虑如何优化这个语句：SELECT SQL_NO_CACHE STRAIGHT_JOIN
     E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related
     FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID
     WHERE E_role_info.roleID = 25669
     ORDER BY E_info.infoID DESC LIMIT 10;而我一直抓住E_info表中的主键为什么不起作用而不放！
在琢磨：SELECT SQL_NO_CACHE STRAIGHT_JOIN 
     E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related 
     FROM `E_info` INNER JOIN E_role_info FORCE INDEX(idx_E_role_info_roleID) ON E_info.infoID = E_role_info.infoID 
     WHERE E_role_info.roleID = 25669 
     ORDER BY E_info.infoID DESC LIMIT 0 , 10;
用先生您提供的语句查413382时也还慢！mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN      E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related      FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID      WHERE E_role_info.roleID = 413382      ORDER BY E_info.infoID DESC LIMIT 10;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42880359 | 2009-04-18 07:54:14 | 413382 |       0 |
| 42880353 | 2009-04-18 08:58:21 | 413382 |       0 |
| 42880351 | 2009-04-18 08:58:20 | 413382 |       0 |
| 42880348 | 2009-04-18 08:58:21 | 413382 |       0 |
| 42880347 | 2009-04-18 08:58:21 | 413382 |       0 |
| 42880341 | 2009-04-18 06:23:47 | 413382 |       0 |
| 42880340 | 2009-04-18 08:57:59 | 413382 |       0 |
| 42880339 | 2009-04-18 07:58:28 | 413382 |       0 |
| 42880338 | 2009-04-18 06:23:47 | 413382 |       0 |
| 42880336 | 2009-04-18 06:23:47 | 413382 |       0 |
+----------+---------------------+--------+---------+
10 rows in set (33.43 sec)
现在我的困扰还是413382,25669这两个值不可兼得！
总之在我的应用中，413382，25669这两个值恰好反映了我们系统中两个极端的情况，我也不知道是否能够达到一个平衡，使得查询这两个值都能够很迅速！这两天也很急躁，不过跟着您的思路也学到了不少知识，非常感谢！现在我就顺着您的思路走，这样应该会更有效！

Answer 45

目前这种优化仅对你 WHERE E_role_info.roleID = 413382 ORDER BY E_role_info.infoID DESC LIMIT 10; where order 都从一表中取数据有作用。否则这个idx_E_role_info_roleID是用不上的了。mysql> select SQL_NO_CACHE STRAIGHT_JOIN
    ->  E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related
    ->  FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID
    ->  WHERE E_role_info.roleID = 413382
    ->  ORDER BY E_role_info.infoID DESC LIMIT 10;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42880359 | 2009-04-18 07:54:14 | 413382 |       0 |
| 42880353 | 2009-04-18 08:58:21 | 413382 |       0 |
| 42880351 | 2009-04-18 08:58:20 | 413382 |       0 |
| 42880348 | 2009-04-18 08:58:21 | 413382 |       0 |
| 42880347 | 2009-04-18 08:58:21 | 413382 |       0 |
| 42880341 | 2009-04-18 06:23:47 | 413382 |       0 |
| 42880340 | 2009-04-18 08:57:59 | 413382 |       0 |
| 42880339 | 2009-04-18 07:58:28 | 413382 |       0 |
| 42880338 | 2009-04-18 06:23:47 | 413382 |       0 |
| 42880336 | 2009-04-18 06:23:47 | 413382 |       0 |
+----------+---------------------+--------+---------+
10 rows in set (0.06 sec)mysql>