CODEWITHSUNDEEP
mysql
andriy
andriy

Reputation: 4164

How to make query with ORDER BY and LIMIT work faster?

In my project I have a loop that runs the same query but with different limit values. In the first 10 loop cycles, query is executed with acceptable speed, but then slow down. Table to which I'm making query has 150K registers and column1 is Integer and my query is:

SELECT * FROM my_table ORDER BY column1 ASC LIMIT 0,1000
...
SELECT * FROM my_table ORDER BY column1 ASC LIMIT 9000,10000

As I understood it sorts all 150K registers each time. But how can I sort register only between indicated range? How can I make this query work faster?

Upvotes: 1

Views: 131

Answers (3)

JustDanyul
JustDanyul

Reputation: 14044

This might be a stupid question, but is column1 indexed?

And think about the second question, it would make sence if the database needs to sort the entire dataset to determine the correct 1000 elements to return :)

Imagine you have 10 numbers in arbitrary order

4, 7, 1, 3, 9, 8, 5, 2, 6, 0

and you want to select 5 numbers, ascending. If you just take the first 5, and sort them you would get

1,3,4,7,9

and if you sorted the whole list first, and then took 5 you would have

0,1,2,3,4

EDIT: Since it is a PK, I don't know if LIMIT is clever enough to determine if your primary key is sequential, otherwise it would certainly need to read them all first. EXPLAIN would tell you if it is.

EDIT BASED ON COMMENT

If it is sequential, maybe something like this would help

SELECT * FROM ... WHERE column1 >= 9000 ORDER BY column1 ASC LIMIT 1000

the where will ensure that it ignores all rows with column1 values under 9000, so you are working on a smaller dataset.

Upvotes: 2

Pentium10
Pentium10

Reputation: 207863

This blog lists all of the choices you can do to speed up pagination queries: http://www.xarg.org/2011/10/optimized-pagination-using-mysql/

Upvotes: 2

Russell Gutierrez
Russell Gutierrez

Reputation: 1385

Try:

Select * from my_table where primary_key in
(SELECT primary_key FROM my_table LIMIT 9000,10000)
ORDER BY column1 ASC;

Upvotes: 0

Related Questions