Pagination is not just a UI feature; it's a database optimization strategy to prevent 'Out of Memory' errors.
1The Performance Trap
Requesting 1,000,000 rows when you only show 10 is a common mistake. It wastes database CPU, network bandwidth, and application memory. Always use LIMIT to match your UI's needs.
2Implementing Pagination
For a website with 10 items per page: Page 1 is LIMIT 10 OFFSET 0. Page 2 is LIMIT 10 OFFSET 10. Page 3 is LIMIT 10 OFFSET 20. The formula is OFFSET = (Page - 1) * Limit.
3TOP in SQL Server
Note: While MySQL, PostgreSQL, and SQLite use LIMIT, Microsoft SQL Server uses SELECT TOP 10. Knowing these small differences is vital when working in cross-platform teams.
