LIMIT and OFFSET for pagination in MySQL - Time & Space Complexity

Time Complexity: LIMIT and OFFSET for pagination

O(n)

Understanding Time Complexity

When using LIMIT and OFFSET in MySQL, we want to know how the time to get results changes as we ask for different pages of data.

We ask: How does the work grow when we move to later pages?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 30;
    

This query gets 10 products starting from the 31st product, ordered by product_id.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning rows to skip OFFSET and then fetching LIMIT rows.
• How many times: The database reads rows up to OFFSET + LIMIT to return the page.

How Execution Grows With Input

As OFFSET grows, the database must skip more rows before returning the page.

Input Size (OFFSET)	Approx. Rows Read
10	About 20 rows (10 skipped + 10 returned)
100	About 110 rows (100 skipped + 10 returned)
1000	About 1010 rows (1000 skipped + 10 returned)

Pattern observation: The work grows roughly in a straight line with OFFSET size.

Final Time Complexity

Time Complexity: O(n)

This means the time to get a page grows linearly with how far into the list you go.

Common Mistake

[X] Wrong: "Using OFFSET doesn't affect performance because we only get a small page of results."

[OK] Correct: The database still reads and skips all rows before the OFFSET, so bigger OFFSET means more work.

Interview Connect

Understanding how LIMIT and OFFSET affect query time helps you explain pagination performance clearly and shows you know how databases handle data retrieval.

Self-Check

"What if we replaced OFFSET with a WHERE condition to start after a known product_id? How would the time complexity change?"