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: Reply•Quote


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


Sorry, only registered users may post in this forum.
Click here to login
powered by phorum

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.