MySQL Forums
Forum List  »  Performance

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.