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

Question

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

没有 ORDER BY 的查询

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.

按 DESC 排序的查询

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 子句的查询比不带 order by 子句的查询快得多？

Answer 1

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

$cfg['MaxRows'] Listed in phpMyAdmin Docs

显式设置 LIMIT 后，查询执行相同的操作(0.0286 秒内得到 259 个结果)。