数据库 关于数据库索引的一个问题: order by...limit 类型 [已解决]
我的 query 是这样的：SELECT * FROM topics ORDER BY updated_at DESC LIMIT 15
现在已经对 updated_at 字段加了索引，但是今天分别在 MySQL 5.5 和 PostgreSQL 9.1 上面测试时发现，PostgreSQL 会用到索引，而 MySQL 却不会，这个怎么回事呢？
MySQL:
mysql> explain select * from topics order by updated_at desc limit 15;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | topics | ALL | NULL | NULL | NULL | NULL | 991 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
PostgreSQL:
development=# explain analyze select * from topics order by updated_at DESC limit 15; QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.57 rows=15 width=52) (actual time=0.049..0.059 rows=15 loops=1)
-> Index Scan Backward using index_topics_on_updated_at on topics (cost=0.00..377.30 rows=10003 width=52) (actual time=0.048..0.053 rows=15 loops=1)
Total runtime: 0.096 ms
(3 rows)
发现问题了，是我的测试数据不一致导致的。上面的例子中，pg 有一万条样本数据，mysql 只有 1 千条，估计这时候 mysql 觉得数据量太小了，不用索引也很快？
刚刚也怀疑数据量的问题，重新测了下，在 mysql 填充一万条样本数据的情况下，也会用到索引：
mysql> explain select * from topics order by updated_at desc limit 15;
+----+-------------+--------+-------+---------------+----------------------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------------------------+---------+------+------+-------+
| 1 | SIMPLE | topics | index | NULL | index_topics_on_updated_at | 8 | NULL | 15 | |
+----+-------------+--------+-------+---------------+----------------------------+---------+------+------+-------+
1 row in set (0.00 sec)