mysql - 没有ORDER BY的查询速度较慢？

我正在我的INNODB数据库上执行以下操作


无需排序的查询

SELECT  SQL_NO_CACHE second_designer
    FROM  itemrow FORCE INDEX(second_designer)
    WHERE  category like '%'
      and  type like '%'
      and  availability like '%'
    GROUP BY  second_designer

259 results in 0.0286 seconds.

按订单查询

SELECT  SQL_NO_CACHE second_designer
    FROM  itemrow FORCE INDEX(second_designer)
    WHERE  category like '%'
      and  type like '%'
      and  availability like '%'
    GROUP BY  second_designer
    ORDER BY  second_designer DESC

259 results in 0.0008 seconds.

指数

ADD INDEX `second_designer` (`second_designer ` , `availability`,
                             `category`, `type`) USING BTREE

说明

id select_type table   type  possible_keys   key         key_len ref  rows Extra
1  SIMPLE      itemrow index second_designer second_designer 608 NULL 44521 Using where; Using index

为什么带有order by子句的查询比没有子句的查询快得多？

最佳答案

差异是由phpMyAdmin添加到所有查询的“隐藏” LIMIT引起的。

$cfg['MaxRows'] Listed in phpMyAdmin Docs

在显式设置LIMIT之后，两个查询都执行相同的操作（259个结果在0.0286秒内）。