Diagnosing and Optimizing CodeIgniter 4 Pagination Performance

Recognizable Condition

Users report that a page listing hundreds of thousands of records takes minutes to load, even though the per‑page item count is modest (e.g., 20 items). The page renders correctly, but the response time is unacceptable. The symptom is usually accompanied by high CPU usage on the database server during pagination requests.

Root Causes in CodeIgniter 4

Cause	Why It Happens
COUNT(*) for total rows	The pagination library runs a separate SELECT COUNT(*) each request to calculate total pages. On very large tables without proper indexes, this is expensive.
Large OFFSET values	MySQL (and SQLite) must scan rows up to the offset before returning the requested slice, leading to full table scans for high page numbers.
Missing ORDER BY index	Ordering without an index forces a full sort, which is costly when combined with LIMIT/OFFSET.
Uncached configuration	When the cache driver is disabled, the library rebuilds the pagination configuration on every request, adding overhead.

Diagnostic Checklist

1. Confirm Pagination Route

   Run php spark routes in the project root. Verify that the route handling the list view includes a parameter for the page number (e.g., /items/(:segment)).

2. Inspect Controller Method

   Open the controller that renders the list. Look for a line like:

   $data['items'] = $this->model->paginate(20, 'default', $pageNumber);
   

   Ensure $pageNumber comes from the route or query string.

3. Check Pagination Config

   Open app/Config/Pagination.php. Verify that $default includes reasonable perPage and total defaults. If total is set to 0, the library will execute a COUNT(*) query.

4. Run EXPLAIN on Paginated Query

   In a MySQL client, run:

   EXPLAIN SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 2000;
   

   Check that the id or created_at column is indexed and that the query uses that index.

5. Measure COUNT(*) Cost

   Execute the COUNT query directly:

   SELECT COUNT(*) FROM users;
   

   Note the execution time. If it’s >1s, consider an alternative.

6. Check Cache Driver

   Open app/Config/Cache.php and confirm $default['handler'] is set (e.g., file or redis). If file is used, ensure app/Cache is writable.

7. Observe Response Time

   Use a tool like curl -w "\nTime: %{time_total}\n" -o /dev/null http://localhost/items/5 to capture total time. Compare before and after changes.

Targeted Fixes

1. Reduce or Cache the Total Count

Instead of letting the library run SELECT COUNT(*) on every request, pre‑compute the total rows and store it in a cache key. Example:

// In controller
$cacheKey = 'users_total_count';
$total = cache($cacheKey);
if ($total === null) {
    $total = $this->model->countAll();
    cache($cacheKey, $total, 3600); // cache for 1 hour
}
$pager = \\Config\Services::pager();
$pager->setTotal($total);
$data['users'] = $this->model->paginate(20, 'default', $pageNumber);

Risk: If the underlying data changes frequently, the cached count may become stale. Adjust TTL accordingly.

2. Use Key‑set Pagination (Seek Method)

MySQL’s OFFSET becomes expensive beyond a few thousand rows. Instead of LIMIT 20 OFFSET 2000, use a key‑set query that starts after the last seen primary key:

// Assuming 'id' is PK
$lastId = $this->request->getGet('last_id');
$builder = $this->model->builder();
if ($lastId) {
    $builder->where('id >', $lastId);
}
$builder->orderBy('id', 'ASC')->limit(20);
$data['users'] = $builder->get()->getResult();

Generate a link for the next page with the last returned id. This approach scales linearly with result size.

3. Ensure Proper Indexes on ORDER BY Columns

When the query orders by created_at, add an index:

ALTER TABLE users ADD INDEX idx_created_at (created_at DESC);

Verify with EXPLAIN that the index is used.

4. Enable Pagination Caching

Cache the rendered pagination links instead of regenerating them each request:

$pager = \\Config\Services::pager();
$pager->setTemplate('default_full'); // or custom
$pager->setOptions(['attributes' => ['class' => 'pagination']]);
$cachedLinks = cache('users_pagination_links');
if ($cachedLinks === null) {
    $cachedLinks = $pager->makeLinks($pageNumber, 20, $total);
    cache('users_pagination_links', $cachedLinks, 600);
}
$data['links'] = $cachedLinks;

5. Use a Dedicated Database View for Counting

For tables with complex joins, create a lightweight view that contains only the primary key and any filtering columns. Count against the view instead of the base table.

When to Escalate

• After applying all fixes, if latency remains above 1s for page requests beyond the first 10 pages.
• If the database server’s CPU consistently exceeds 80% during pagination.
• When the application must support real‑time data updates and stale counts are unacceptable.

At this point, consider database sharding, read replicas, or moving pagination logic to a dedicated microservice that can cache results more aggressively.

Practical Verification Steps

1. Run php spark make:controller TestPagination to scaffold a test controller.
2. Implement the controller with the above fixes and load the route in a browser.
3. Use php spark db:explain or a tool like mysqladmin processlist to monitor query execution.
4. Compare response times with curl -w "\nTime: %{time_total}\n" -o /dev/null http://localhost/test-pagination/5 before and after.
5. Check the app/Cache directory (or Redis) for cached entries to confirm caching is active.

By following this diagnostic path, you can isolate the performance bottleneck in CodeIgniter 4’s pagination and apply the most appropriate fix—whether that’s caching the total count, switching to key‑set pagination, or ensuring indexes are in place.