Limit in mysql is timing out

Question

In a nutshell, this query returns 890738 entries

SELECT * 
FROM  `cms_question_report` 
WHERE  `doa` <  '2014-12-16 11:48:13'

And it does it that in around 2 seconds

After trying to cut it into 4 chunks, this query times out and produces an error

SELECT * 
FROM  `cms_question_report` 
WHERE  `doa` <  '2014-12-16 11:48:13'
LIMIT 222684

Here's the error:

Error in Processing Request
Error code: 500
Error text: Internal Server Error

In my basic understanding shouldn't the second one run faster as it has a lower limit to the data it's fetching?

Another test:

SELECT * 
FROM  `cms_question_report` 
WHERE  `doa` <  '2014-12-16 11:48:13'
LIMIT 2

That worked smoothly

I tried about a minute ago and it's still loading, probably will end up with the same result, although limit 2 worked. — Naguib Ihab, Mar 17 '15 at 03:45
see this http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down — Roy Shmuli, Mar 17 '15 at 03:48
@RoyShmuli thanks I'm checking out the link in the answer, it seems like it might do the trick. The Limit 10000 timed out and gave the same error by the way. — Naguib Ihab, Mar 17 '15 at 03:53

score 0 · Answer 1 · answered Mar 17 '15 at 03:47

0

No it will not run faster. The db has to first get all the results and ORDER them before applying a LIMIT. I know this seems a waste of resource, why not just stop after getting LIMIT rows? Because they have to be ordered, even when that order is implicit because you have not specified an explicit order. The implicit order is the PK order of the main table in the query, but the WHERE clause means that the rows will not automaticaly be found in that order. So all rows have to be found, then ordered, then limited.

answered Mar 17 '15 at 03:47

David Soussan

2,698
1
16
19

mm so it's actually taking more time. What do you mean by "why not just stop after getting LIMIT rows?", I have to have the WHERE clause. Is there another method to divide large chunk of data other than LIMIT? – Naguib Ihab Mar 17 '15 at 03:50
I meant the logic of the database engine of course, basic rhetorical english. Sorry of english is not your first language so you did not understand it. Why does the database not stop after finding limit rows? is the full expanded meaning. Have a look at the question @Roy linked and then the article link in the answers. – David Soussan Mar 17 '15 at 03:54

Naguib Ihab · Accepted Answer · 2015-03-18T00:20:51.217

0

Found the solution thanks to Roy Shmuli. The query that solved the issue was:

SELECT Y.* FROM (SELECT id FROM `cms_question_report` WHERE doa < '2014-12-16 11:48:13' ORDER BY id LIMIT 295354) X JOIN `cms_question_report` Y ON X.id = Y.id

And I got it from this link: http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

edited Mar 18 '15 at 00:20

answered Mar 18 '15 at 00:08

Naguib Ihab

4,259
7
44
80

Limit in mysql is timing out

2 Answers2