Query Builder limit and offset behavior with high-volume datasets

Answer

The Query Builder’s limit() and offset() methods simply add LIMIT ? OFFSET ? to the generated SQL. The database must read and discard all rows up to the offset before returning the limited set, so execution time grows roughly linearly with the offset value.

Confirmed facts

• No internal buffering or caching is performed by CodeIgniter; the offset value is passed directly to the driver.
• For MySQL, PostgreSQL, and SQLite the plain LIMIT/OFFSET clause causes a full scan up to the offset unless an index can satisfy the ordering and the planner can use an index‑only scan, but the rows still need to be skipped.
• The architectural limit is therefore the point where the scan‑and‑skip cost outweighs acceptable response time; in practice this becomes noticeable when offsets exceed a few hundred thousand rows on typical hardware.

Steps to verify and mitigate

1. Capture the generated query: $this->db->last_query(); after building the query with limit/offset.
2. Run the query with EXPLAIN (or EXPLAIN ANALYZE) to see the cost of scanning/skipping rows.
3. Measure response time for increasing offset values (e.g., 0, 100k, 500k) to confirm the linear trend.
4. If the trend is unacceptable, replace offset pagination with keyset pagination:
1. Determine a stable, unique ordering column (usually the primary key or a timestamp).
2. Store the last seen value from the previous page.
3. Build the next page with $this->db->where('id >', $lastId)->orderBy('id')->limit($pageSize); (adjust column and operator as needed).
5. Verify the new query produces the same ordering and does not contain OFFSET.

Missing diagnostic detail

To give a precise recommendation, please confirm which database driver you are using (e.g., MySQL, PostgreSQL, SQL Server).