How can I speed up a MySQL query with a large offset in the LIMIT clause?

mysql performance limit
ZA. picture ZA. · Aug 7, 2009 · Viewed 25.8k times · Source

I'm getting performance problems when LIMITing a mysql SELECT with a large offset:

SELECT * FROM table LIMIT m, n;

If the offset m is, say, larger than 1,000,000, the operation is very slow.

I do have to use limit m, n; I can't use something like id > 1,000,000 limit n.

How can I optimize this statement for better performance?

Answer

Paul Dixon picture Paul Dixon · Aug 7, 2009

Perhaps you could create an indexing table which provides a sequential key relating to the key in your target table. Then you can join this indexing table to your target table and use a where clause to more efficiently get the rows you want.

#create table to store sequences
CREATE TABLE seq (
   seq_no int not null auto_increment,
   id int not null,
   primary key(seq_no),
   unique(id)
);

#create the sequence
TRUNCATE seq;
INSERT INTO seq (id) SELECT id FROM mytable ORDER BY id;

#now get 1000 rows from offset 1000000
SELECT mytable.* 
FROM mytable 
INNER JOIN seq USING(id)
WHERE seq.seq_no BETWEEN 1000000 AND 1000999;

Related questions

Why does MYSQL higher LIMIT offset slow the query down?

Scenario in short: A table with more than 16 million records [2GB in size]. The higher LIMIT offset with SELECT, the slower the query becomes, when using ORDER BY *primary_key* So SELECT * FROM large ORDER BY `id` LIMIT 0, 30 takes far …

Read More
MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two?

Read More
Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More