我试图避免文件排序,但没有从内部查询中删除它。如果我把条件移到外查询,那么它什么也不会显示。
Create table articles (
article_id Int UNSIGNED NOT NULL AUTO_INCREMENT,
editor_id Int UNSIGNED NOT NULL,
published_date Datetime,
Primary Key (book_id)) ENGINE = InnoDB;
Create Index published_date_INX ON articles (published_date);
Create Index editor_id_INX ON articles (editor_id);
EXPLAIN SELECT article_id, article_date FROM articles AA INNER JOIN
(
SELECT article_id
FROM articles
WHERE editor_id=1
ORDER BY published_date DESC
LIMIT 100, 5
) ART USING (article_id);
+----+-------------+------------+--------+---------------+-----------+---------+----------------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+-----------+---------+----------------+--------+----------------+
| 1 | | PRIMARY | <derived2> | ALL | NULL | NULL | NULL NULL | 5 | |
| 1 | | PRIMARY | AA | eq_ref | PRIMARY | PRIMARY | 4 ART.article_id | 1 | |
| 2 | | DERIVED | articles | ALL | editor_id | editor_id | 5 | 114311 | Using filesort |
+----+-------------+------------+--------+---------------+-----------+---------+----------------+--------+----------------+
3 rows in set (30.31 sec)
有什么建议如何从这个查询中删除文件排序?
也许你可以尝试在editor_id, published_date
上添加一个索引。
create index edpub_INX on articles (editor_id, published_date);
关于你的内部查询:
SELECT article_id
FROM articles
WHERE editor_id=1
ORDER BY published_date DESC
LIMIT 100, 5
MySQL的查询规划器可能认为先用editor_id
过滤(使用索引)再用published_date
排序比先用published_date_INX
再用editor_id
过滤要好。查询计划器可能是正确的。
所以,如果你想"帮助"这个特定的查询,在editor_id, published_date
上创建一个索引,看看它是否能帮助你的查询运行得更快。