LIMIT and OFFSET for pagination in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What rows does this query return?

Consider a table Employees with 10 rows ordered by id ascending. What rows does this query return?

SELECT * FROM Employees ORDER BY id LIMIT 3 OFFSET 4;

MySQL

SELECT * FROM Employees ORDER BY id LIMIT 3 OFFSET 4;

A Rows with id 5, 6, 7

B Rows with id 4, 5, 6

C Rows with id 1, 2, 3

D Rows with id 7, 8, 9

💡 Hint

OFFSET skips the first N rows, LIMIT picks how many rows after that.

OFFSET 4 skips first 4 rows (id 1 to 4), then LIMIT 3 picks next 3 rows (id 5, 6, 7).

📝 Syntax

intermediate

Which query has correct syntax for pagination?

Which of these queries correctly uses LIMIT and OFFSET in MySQL to get 5 rows starting from the 10th row?

A SELECT * FROM Products LIMIT 5 OFFSET 10;

B SELECT * FROM Products LIMIT 10, 5;

C SELECT * FROM Products OFFSET 10 LIMIT 5;

D SELECT * FROM Products LIMIT 5, OFFSET 10;

💡 Hint

MySQL supports two LIMIT syntaxes: LIMIT count OFFSET skip or LIMIT skip,count.

Option A uses LIMIT 5 OFFSET 10 which is valid syntax. Option A uses LIMIT 10,5 which means skip 10, take 5, and is also valid in MySQL. But since the question asks for correct syntax to get 5 rows starting from 10th row, option A is preferred as it is clearer. Option A and D are invalid syntax.

❓ optimization

advanced

How to optimize pagination for large OFFSET values?

You have a table with millions of rows. Using LIMIT 10 OFFSET 1000000 is slow. Which approach improves performance for pagination?

A Remove ORDER BY clause to speed up query.

B Increase OFFSET value to skip more rows.

C Use ORDER BY random() with LIMIT 10.

D Use WHERE clause with indexed column to filter rows after last seen id.

💡 Hint

OFFSET skips rows but still scans them internally, which is slow for large values.

Using WHERE with an indexed column (like id > last_seen_id) allows the database to jump directly to the needed rows without scanning all skipped rows. Increasing OFFSET just makes it slower. ORDER BY random() is very slow. Removing ORDER BY changes result order and may not be acceptable.

🧠 Conceptual

advanced

Why does OFFSET affect query performance?

Why does using a large OFFSET value in a query like SELECT * FROM table LIMIT 10 OFFSET 1000000 cause slow performance?

A Because LIMIT forces the database to sort all rows.

B Because OFFSET causes the database to lock all rows.

C Because the database must scan and discard all rows before the OFFSET.

D Because OFFSET disables use of indexes.

💡 Hint

Think about what OFFSET means internally for the database engine.

OFFSET tells the database to skip a number of rows, so it must scan and discard those rows before returning the requested rows. This scanning causes slow performance for large OFFSET values.

🔧 Debug

expert

Identify the error in this pagination query

This query is intended to return 10 rows starting from the 20th row, but it causes a syntax error:

SELECT * FROM Orders LIMIT OFFSET 20, 10;

What is the error?

A LIMIT must be a single number without OFFSET.

B The syntax should be LIMIT 10 OFFSET 20 without a comma.

C LIMIT and OFFSET are reversed; OFFSET should come after LIMIT with a number.

D OFFSET cannot be used with LIMIT in MySQL.

💡 Hint

Check the correct order and syntax of LIMIT and OFFSET in MySQL.

The query uses 'LIMIT OFFSET 20, 10' which is invalid syntax. The correct syntax is either 'LIMIT 10 OFFSET 20' or 'LIMIT 20, 10' (meaning OFFSET 20, LIMIT 10). The comma syntax does not use the word OFFSET.