mysql5.1用不上联合索引，A能用B不能用

问题是这样：
有A、B两个表，它们都包含id,show_state两个字段。id是主键。
现在有这样一个语句：
select id form A where show_state=1 order by id desc limit 400000,20
这时这个语句非常慢。所以要建要样一个联合索引，show_state在前，id在后。建完了以后此语句变得很快。现在问题是表B在这些点上跟表A在样，结果表B却用不上这样的联合索引，用explain查看，计划用的是联合索引，但在执行时却改用了id主键索引。我用Force Index(B_show_id)强制使用联合索引，结果很慢。
这是为什么？我想应该有很多人遇到过吧。

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

两个表的数据都一样么？mysql索引不一定都会用的，会根据数据重复率和大小来选择是否使用，另外，mysql执行sql不一定是按照explain出来的结果执行的
贴出你的 explain 和 show index from xx 的结果以供分析。
mysql> show index from home_site;
+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| home_site |          0 | PRIMARY         |            1 | site_id     | A         |      573595 |     NULL | NULL   |      | BTREE      |         |
| home_site |          0 | site_id         |            1 | site_id     | A         |      573595 |     NULL | NULL   |      | BTREE      |         |
| home_site |          1 | site_createtime |            1 | create_time | A         |      286797 |     NULL | NULL   |      | BTREE      |         |
| home_site |          1 | site_map_id     |            1 | map_id      | A         |       95599 |     NULL | NULL   |      | BTREE      |         |
| home_site |          1 | site_user_id    |            1 | us_id       | A         |       38239 |     NULL | NULL   | YES  | BTREE      |         |
| home_site |          1 | site_show_id    |            1 | show_state  | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| home_site |          1 | site_show_id    |            2 | site_id     | A         |      573595 |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)mysql> show index from home_route;
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| home_route |          0 | PRIMARY      |            1 | rt_id       | A         |       73314 |     NULL | NULL   |      | BTREE      |         |
| home_route |          0 | rt_id        |            1 | rt_id       | A         |       73314 |     NULL | NULL   |      | BTREE      |         |
| home_route |          1 | route_map_id |            1 | map_id      | A         |       18328 |     NULL | NULL   | YES  | BTREE      |         |
| home_route |          1 | route_userid |            1 | us_id       | A         |       10473 |     NULL | NULL   |      | BTREE      |         |
| home_route |          1 | rt_show_id   |            1 | show_state  | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| home_route |          1 | rt_show_id   |            2 | rt_id       | A         |       73314 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)
mysql> explain select site_id id from home_site where show_state=1 order by id desc limit 400000,20;
+----+-------------+-----------+------+---------------+--------------+---------+-------+--------+--------------------------+
| id | select_type | table     | type | possible_keys | key          | key_len | ref   | rows   | Extra                    |
+----+-------------+-----------+------+---------------+--------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | home_site | ref  | site_show_id  | site_show_id | 1       | const | 571714 | Using where; Using index |
+----+-------------+-----------+------+---------------+--------------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)mysql> explain select rt_id id from home_route where show_state=1 order by id desc limit 40000,20;
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | home_route | index | rt_show_id    | PRIMARY | 4       | NULL | 40020 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)
其中，home_site表有50万数据，home_route有6万。
home_site能用上联合索引，执行时间是0.1秒左右。
home_route表则自动用了rt_id的主键来当索引了，结果在0.7秒。
强制home_route用rt_show_id，结果执行时间是1.2秒。
analze table home_site
analze table home_route重分析一下索引，再试一下。
理论上应该第一个不走索引，第二个走。结果好象刚好相反
mysql>  analyze table home_site;
+-------------------+---------+----------+-----------------------------+
| Table             | Op      | Msg_type | Msg_text                    |
+-------------------+---------+----------+-----------------------------+
| mapbar1.home_site | analyze | status   | Table is already up to date |
+-------------------+---------+----------+-----------------------------+
1 row in set (0.01 sec)mysql>  analyze table home_route;
+--------------------+---------+----------+-----------------------------+
| Table              | Op      | Msg_type | Msg_text                    |
+--------------------+---------+----------+-----------------------------+
| mapbar1.home_route | analyze | status   | Table is already up to date |
+--------------------+---------+----------+-----------------------------+
1 row in set (0.01 sec)这个结果有什么用处，我没用过这个
ＡＮＡＬＹＺＥ完后再explain 和 show index 一下。
你应该EXPLAIN下，就知道为什么不用联合索引了！优化器是比人聪明的。