Re: mysql order by limit too slow, faster when making select a sub query
Posted by:
Mathew V
Date: March 20, 2013 10:37PM
EXPLAIN SELECT *
FROM (
SELECT up.`id`,
up.`email`,
up.`username`,
up.`name`,
up.`gender`,
DATE_FORMAT(up.`createdOn`, '%d-%m-%Y') AS `createdDate`,
up.accountStatus`, ucd.`landphone`
FROM `profiles` up JOIN `contact_details` ucd ON (up.`id` = ucd.`userId`)
WHERE up.`accountStatus` = 'active' )a
ORDER BY `name` DESC LIMIT 1000000, 20
+----+-------------+------------+--------+---------------+---------+---------+--------------------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+--------------------+--------+----------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 116371 | Using filesort |
| 2 | DERIVED | ucd | ALL | PRIMARY | NULL | NULL | NULL | 327798 | |
| 2 | DERIVED | up | eq_ref | PRIMARY | PRIMARY | 4 | xxx.ucd.userId | 1 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+--------------------+--------+----------------+
3 rows in set (3.29 sec)
EXPLAIN SELECT up.`id`,
up.`email`,
up.`username`,
up.`name`,
up.`gender`,
DATE_FORMAT(up.`createdOn`, '%d-%m-%Y') AS `createdDate`,
up.`accountStatus`,
ucd.`landphone`
FROM `profiles` up
JOIN `contact_details` ucd ON (up.`id` = ucd.`userId`)
WHERE up.`accountStatus` = 'active'
ORDER BY `name` DESC LIMIT 1000000, 20
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+-----------------------------+
| 1 | SIMPLE | up | ALL | PRIMARY | NULL | NULL | NULL | 345619 | Using where; Using filesort |
| 1 | SIMPLE | ucd | eq_ref | PRIMARY | PRIMARY | 4 | xxx.up.id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+-----------------------------+
There is nothing special about 100000 th row. That was put as an example, it is used for pagination.
Also, the CPU goes to 100% whenever the query is run. Is this normal?
Edited 1 time(s). Last edit at 03/20/2013 10:41PM by Mathew V.