Why does MYSQL higher LIMIT offset slow the query down?

by

I had the exact same problem myself. Given the fact that you want to collect a large amount of this data and not a specific set of 30 you’ll be probably running a loop and incrementing the offset by 30.

So what you can do instead is:

  1. Hold the last id of a set of data(30) (e.g. lastId = 530)
  2. Add the condition WHERE id > lastId limit 0,30

So you can always have a ZERO offset. You will be amazed by the performance improvement.

More Related Contents:

  1. How can I speed up a MySQL query with a large offset in the LIMIT clause?
  2. Mysql delete statement with limit
  3. MyISAM versus InnoDB [closed]
  4. How can i optimize MySQL’s ORDER BY RAND() function?
  5. MySQL ‘Order By’ – sorting alphanumeric correctly
  6. MySQL Orderby a number, Nulls last
  7. How does MySQL process ORDER BY and LIMIT in a query?
  8. Order a MySQL table by two columns
  9. Maximum number of records in a MySQL database table
  10. GROUP_CONCAT ORDER BY
  11. MySQL – UPDATE query with LIMIT
  12. Why is MySQL InnoDB insert so slow?
  13. Select the 3 most recent records where the values of one column are distinct
  14. MySQL stored procedures use them or not to use them
  15. MySQL order by “best match”
  16. MySql views performance [closed]
  17. MySql Query: Select top 3 rows from table for each category
  18. “SELECT COUNT(*)” is slow, even with where clause
  19. How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?
  20. MySQL load data infile – acceleration?
  21. Mysql: Order by like?
  22. is php sort better than mysql “order by”?
  23. How can “set timestamp” be a slow query?
  24. UPDATE with ORDER BY and LIMIT not working in MYSQL
  25. Would it help to add index to BIGINT column in MySQL?
  26. ALTER TABLE ADD COLUMN takes a long time
  27. Mysql performance on 6 million row table
  28. Composite Primary Key performance drawback in MySQL
  29. MYSQL Select One Random record from each Category
  30. mysql order by, null first, and DESC after

Leave a Comment