Pieter Pieter - 6 months ago 14
MySQL Question

Query without ORDER BY slower?

I'm executing the following on my INNODB database



QUERY WITHOUT 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.





QUERY WITH ORDER BY 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.





INDEX

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


EXPLAIN

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




Why is the query with order by clause so much faster than the one without?

Answer

The difference was caused by the 'hidden' LIMIT phpMyAdmin adds to all queries.

$cfg['MaxRows'] Listed in phpMyAdmin Docs

After setting a LIMIT explicitly the queries both perform the same (259 results in 0.0286 seconds).

Comments