What is LIMIT and OFFSET in MySQL?
Answer
LIMIT restricts the number of rows returned by a query. OFFSET skips a specified number of rows before returning results. Together they enable pagination. Syntax: SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20; — returns rows 21-30. Alternative shorthand: LIMIT 20, 10 — skip 20, take 10 (confusingly reversed — first is offset, second is count). Pagination formula: for page N with page_size rows: OFFSET = (N - 1) * page_size. Example: page 3, 10 per page: LIMIT 10 OFFSET 20. Performance concern: OFFSET-based pagination is slow on large tables because MySQL must read and discard all offset rows before returning results — scanning 10,000 rows to return 10 is wasteful. For large datasets, use cursor-based pagination (keyset pagination): instead of OFFSET, use a WHERE clause based on the last seen ID: WHERE id > last_seen_id ORDER BY id LIMIT 10 — much faster because it uses the index.