MySQL ORDER BY DESC很快，但是ASC却很慢 [英] MySQL ORDER BY DESC is fast but ASC is very slow

问题描述

我完全迷住了这个.由于某种原因，当我按DESC排序此查询时，它非常快，但如果按ASC排序，则非常慢.

I'm completely stumped on this one. For some reason when I sort this query by DESC it's super fast, but if sorted by ASC it's extremely slow.

这大约需要150毫秒:

This takes about 150 milliseconds:

SELECT posts.id
FROM posts USE INDEX (published)
WHERE posts.feed_id IN ( 4953,622,1,1852,4952,76,623,624,10 )
ORDER BY posts.published DESC
LIMIT 0, 50;

这大约需要32秒:

SELECT posts.id
FROM posts USE INDEX (published)
WHERE posts.feed_id IN ( 4953,622,1,1852,4952,76,623,624,10 )
ORDER BY posts.published ASC
LIMIT 0, 50;

两个查询的EXPLAIN相同.

The EXPLAIN is the same for both queries.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  posts   index   NULL    published   5   NULL    50  Using where

我已将其跟踪为"USE INDEX(已发布)".如果我将其删除，则两种方式的性能相同.但是EXPLAIN显示查询总体上效率较低.

I've tracked it down to "USE INDEX (published)". If I take that out it's the same performance both ways. But the EXPLAIN shows the query is less efficient overall.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  posts   range   feed_id feed_id 4   \N  759 Using where; Using filesort

这是桌子.

CREATE TABLE `posts` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `feed_id` int(11) NOT NULL,
  `post_url` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` blob,
  `author` varchar(255) DEFAULT NULL,
  `published` int(12) DEFAULT NULL,
  `updated` datetime NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `post_url` (`post_url`,`feed_id`),
  KEY `feed_id` (`feed_id`),
  KEY `published` (`published`)
) ENGINE=InnoDB AUTO_INCREMENT=196530 DEFAULT CHARSET=latin1;

有没有解决的办法?谢谢！

Is there a fix for this? Thanks!

推荐答案

您的索引已按降序排序，因此当您要求升序时，它需要做更多的工作才能将其恢复为原来的顺序

Your index is sorted desc so when you ask for ascending it needs to do a lot more work to bring it back in that order

这篇关于MySQL ORDER BY DESC很快，但是ASC却很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！