Reza Golkhandani

Don't Ever Use LIMIT Again.

Main Point: Offset-based pagination using LIMIT and OFFSET is simple but inefficient for large datasets. Cursor-based pagination is faster and more scalable.

Key Concepts

• Offset-based pagination: Uses LIMIT ... OFFSET .... Slows down for deep pages because it must scan and skip rows. Example: LIMIT 10 OFFSET 10000 took ~57ms.
• Cursor-based pagination: Uses a unique identifier (e.g., user_id) to resume fetching. Example: WHERE user_id > 'last_id' LIMIT 10 took ~7ms.

Ordering with Non-Unique Fields

If ordering by a non-unique field like first_name, combine it with a unique field:

WHERE
  first_name >= 'Jammie'
  AND (
    user_id > 'last_user_id'
    OR first_name > 'Jammie'
  )
ORDER BY first_name, user_id
LIMIT 10

Conclusion

Cursor-based pagination offers much better performance, especially with large datasets. Offset pagination is okay for small tables or admin tools, but should be avoided in high-scale production queries.

Connect with Me