Diagnosing N+1 Queries in Yii 2 List Pages: A Practical Checklist

The condition: page time grows with row count

A Yii 2 list page that renders quickly with 20 rows but slows down as the table grows is often suffering from an N+1 query pattern. The database monitoring tool or the Yii debug toolbar shows many nearly identical SELECT statements, each with a different foreign key value. The page may still return correct data, which makes the problem easy to miss until load increases.

This guide assumes Yii 2.x with ActiveQuery and ActiveDataProvider. Yii 1 ActiveRecord behaves differently and is not covered here. The goal is to identify whether the cost comes from lazy relation loading, pagination configuration, missing indexes, or object hydration, then apply the smallest fix that matches the finding.

Symptom-to-cause table

Symptom	Likely cause	First check
Repeated SELECT ... WHERE fk = ? per row	Lazy loading a relation inside a view loop	View file foreach; controller with() usage
Query count constant but page still slow	pageSize too large or missing index	DataProvider pagination; EXPLAIN on main query
Memory grows with rows	Full ActiveRecord objects loaded for read-only grid	Whether asArray() or select() is used
Slow query log shows full table scan	Missing index on foreign key column	Schema indexes on FK columns used in joins

Ordered checks

1. Enable the debug toolbar in staging only. The yii2-debug module records query count and execution time per request. Never enable it in production: it adds overhead and can expose data. Load the target list page and note the query count.
2. Inspect the controller action. Find the ActiveQuery that feeds the ActiveDataProvider. Check whether with() is called before the provider is created. Eager loading must be attached to the query, not applied after pagination.
3. Review the view file. Look for property access on relations inside a foreach, such as $post->author->name. Each access can trigger a separate query if the relation was not eager loaded.
4. Check DataProvider pagination. Confirm pageSize is bounded. A very large page size multiplies the effect of any per-row query and increases memory use. Also check whether totalCount is being computed unnecessarily on every request.
5. Review the database schema. For every foreign key used in the relation, confirm an index exists. Run EXPLAIN on the main query and on a representative relation query to see whether the index is used.

A concrete example: eager loading before pagination

Suppose a Post model has a getAuthor() relation and the list view shows the author name. The controller might look like this:

// In the controller action, before creating the DataProvider
$query = Post::find()
    ->with(['author'])          // eager load the relation used in the view
    ->orderBy(['created_at' => SORT_DESC]);

$dataProvider = new ActiveDataProvider([
    'query' => $query,
    'pagination' => [
        'pageSize' => 20,       // keep the page bounded
    ],
]);

The view can then access the relation without triggering a query per row:

<?php foreach ($dataProvider->getModels() as $post): ?>
    <?= Html::encode($post->author->name) ?>
<?php endforeach; ?>

For read-only grids, asArray() avoids hydrating full ActiveRecord objects:

$query = Post::find()
    ->with(['author'])
    ->asArray();

When using asArray(), the view must use array access such as $post['author']['name'] instead of object properties.

Fixes tied to findings

• Finding: repeated relation queries. Add with(['relation']) to the ActiveQuery before creating the DataProvider. This is the primary fix for N+1.
• Finding: large page size. Reduce pageSize to a value that keeps query count and memory predictable. The right number depends on row width and server memory; verify in staging.
• Finding: missing index. Add an index on the foreign key column used by the relation. Use the database migration tool or a schema change reviewed by a DBA.
• Finding: unnecessary object hydration. Use asArray() or select() for read-only grids to reduce memory and CPU.
• Finding: repeated identical relation data. Cache the relation result if the same parent records appear across many rows and the data changes infrequently.

Verification

After each change, repeat the same measurement in staging:

1. Load the list page with the debug toolbar enabled and record query count and execution time.
2. Compare before and after. The expected result is a drop from N+1 queries to a small constant number, not a claim that this exact count will occur in your application.
3. Run EXPLAIN on the main query and a relation query to confirm index usage after adding indexes or changing pagination.
4. Check application logs for the repeated SELECT pattern. It should no longer appear per row.

Escalation criteria

Stop tuning at the application layer and escalate if any of these remain true after the fixes above:

• Query count stays high after with() is confirmed to be on the query that feeds the DataProvider.
• EXPLAIN shows a bad plan or a full table scan despite an index on the foreign key.
• Latency persists under load after pagination is bounded and eager loading is in place.

At that point the issue may involve query planner statistics, database server resources, or a relation design that needs a join or a denormalized read model.

Limitations and rollback

This guide assumes Yii 2.x. Yii 1 uses a different ActiveRecord and query builder, so the same with() advice may not apply. The debug toolbar must stay out of production. Eager loading can increase memory use because it loads related rows up front; verify with staging data volume before deploying.

If you added a database index, the rollback is to drop that index. If you changed code or pageSize, revert the commit or configuration value. No data migration is required for the application-layer fixes described here.

Because behavior depends on your Yii version, database engine, and data distribution, treat the expected query-count drop as a hypothesis to verify in staging rather than a guaranteed outcome.