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 904 Mathew V 03/20/2013 01:54AM
Re: mysql order by limit too slow, faster when making select a sub query 408 Øystein Grøvlen 03/20/2013 05:39AM
Re: mysql order by limit too slow, faster when making select a sub query 426 Rick James 03/20/2013 09:07AM
Re: mysql order by limit too slow, faster when making select a sub query 1005 Mathew V 03/20/2013 10:37PM
Re: mysql order by limit too slow, faster when making select a sub query 565 Rick James 03/21/2013 09:16AM
Re: mysql order by limit too slow, faster when making select a sub query 537 Øystein Grøvlen 03/22/2013 04:03AM
Re: mysql order by limit too slow, faster when making select a sub query 447 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.