Skip navigation links

MySQL Forums :: Performance :: mysql order by limit too slow, faster when making select a sub query


Advanced Search

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


Subject Views Written By Posted
mysql order by limit too slow, faster when making select a sub query 1029 Mathew V 03/20/2013 01:54AM
Re: mysql order by limit too slow, faster when making select a sub query 462 Øystein Grøvlen 03/20/2013 05:39AM
Re: mysql order by limit too slow, faster when making select a sub query 487 Rick James 03/20/2013 09:07AM
Re: mysql order by limit too slow, faster when making select a sub query 1272 Mathew V 03/20/2013 10:37PM
Re: mysql order by limit too slow, faster when making select a sub query 651 Rick James 03/21/2013 09:16AM
Re: mysql order by limit too slow, faster when making select a sub query 611 Øystein Grøvlen 03/22/2013 04:03AM
Re: mysql order by limit too slow, faster when making select a sub query 502 Rick James 03/23/2013 12:17AM


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.