MySQL Forums
Forum List  »  Performance

mysql order by limit too slow, faster when making select a sub query
Posted by: Mathew V
Date: March 20, 2013 01:54AM

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

takes around 15 seconds while

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
takes around 2.5 seconds for a total of 350000 records.

name field is indexed and accountStatus is an enum field. Why the increase in speed when breaking the select into a sub query and is there any other way to optimize the above order by limit query?

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.