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

by

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;

More Related Contents:

  1. Why does MYSQL higher LIMIT offset slow the query down?
  2. MyISAM versus InnoDB [closed]
  3. MYSQL OR vs IN performance
  4. How to Perform an UPSERT so that I can use both new and old values in update part
  5. JDBC batch insert performance
  6. Using variable in a LIMIT clause in MySQL
  7. SQL Performance UNION vs OR
  8. Combining UNION and LIMIT operations in MySQL query
  9. Does adding ‘LIMIT 1’ to MySQL queries make them faster when you know there will only be 1 result?
  10. MySQL: Fastest way to count number of rows
  11. Why is MySQL InnoDB insert so slow?
  12. MySQL vs MongoDB 1000 reads
  13. MySQL “IN” operator performance on (large?) number of values
  14. Optimal MySQL settings for queries that deliver large amounts of data?
  15. What is better in MYSQL count(*) or count(1)?
  16. MySQL JOIN with LIMIT 1 on joined table
  17. DOs and DONTs for Indexes [closed]
  18. MySQL performance: multiple tables vs. index on single table and partitions
  19. MySQL view performance
  20. How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?
  21. MySQL: Inner join vs Where [duplicate]
  22. Mysql delete statement with limit
  23. Higher cardinality column first in an index when involving a range?
  24. Does Order of Fields of Multi-Column Index in MySQL Matter
  25. MySQL: Many tables or many databases?
  26. sql group by versus distinct
  27. UPDATE with ORDER BY and LIMIT not working in MYSQL
  28. ALTER TABLE ADD COLUMN takes a long time
  29. Disadvantages of quoting integers in a Mysql query?
  30. Optimizing MySQL for ALTER TABLE of InnoDB

Leave a Comment