Mysql offset infinite rows

Offsetting like a magician using LIMIT

Handling MySQL LIMIT and OFFSET

In MySQL, meeting the requirement of an infinite offset isn't straightforward. Nevertheless, a simple manipulation of the LIMIT clause can work like magic:

SELECT * FROM your_table_name LIMIT 10, 18446744073709551615; -- Hello from the other side, I'm skipping the first 10 rows! 🎤


This command will fetch rows starting at the 11th record, displaying all rows till the end of the table, akin to an infinite offset.

Large tables and their OFFSET woes

Large tables could cause a performance snag with this approach as OFFSET counts through rows from the top. For these behemoths, harness the power of keyset pagination or conditional WHERE clause instead:

SELECT * FROM your_table_name WHERE id > last_seen_id ORDER BY id LIMIT 10; -- This is the fast and furious method of skipping rows 🚀


This is a preferred approach as it leverages an index scan over a row-by-row scan, improving performance.

Best cases for your toolkit

Identifying the perfect magic trick

Like a magician with a bag of tricks, knowing when to use the right one can save the day. Here are a few scenarios where manipulating the LIMIT clause can work wonders:

• Batch processing - Fetching rows in batches while skipping a pre-determined number.
• Reporting - Creating reports where a specific range of data is excluded systematically.
• Replication - Fetching selective data from a table in replication tasks.

Mind the gotchas, every trick has a catch

As with any magic trick, there are caveats to manipulating the LIMIT clause:

• Performance impact - The OFFSET goes row by row; an increased offset could impact performance.
• Large datasets - The bigger your data, the more you need to monitor performance.
• Business logic dependence - Maintaining statelessness or repeatability can be tricky if you attach offsets to larger business logic.

Better methods than OFFSET

The Seek Method - a knight in shining armor!

When large data sets make offset problematic, the seek method comes to the rescue.

SELECT * FROM your_table_name WHERE id > last_retrieved_id ORDER BY id LIMIT 100; -- Skipping rows like playing hopscotch 😎


Here, the query completely bypasses counting rows—it's quicker than playing hopscotch!

Window Functions - peering into complicated scenarios

Complex scenarios may require something more tailored. Enter window functions:

SELECT *,
       ROW_NUMBER() OVER (ORDER BY some_column) as seqnum
FROM your_table_name
HAVING seqnum > N; -- I spy with my little eye, a method to skip rows... 🕵🏻‍♀️


Well, now you have an offset which also gives a sequence number to rows.