Diagnosing N+1 Queries in Yii2 ActiveRecord and Fixing Them with Eager Loading

The recognizable condition

A list page that should be fast renders slowly and the Yii debug toolbar database panel shows one query for the parent list plus the same relation query repeated once per row. The pattern is 1 + N queries where N is the number of displayed models. The total query count grows linearly with page size.

Lazy loading is the default behavior of Yii2 ActiveRecord relations. Accessing $model->relation inside a loop triggers a separate SQL query for each iteration instead of a single batched query.

Cause to symptom table

Ordered checks

1. Enable the debug module database panel. In a development application, ensure debug is enabled and the DB panel is visible. Load the affected list page and note the total query count and the SQL list. The panel shows duration per query and parameters. This is per-request and includes queries from widgets and behaviors, not only controller code.

2. Count queries per request. Compare the query count for 10 items vs 20 items. If the count grows by roughly the number of items per relation, you have N+1.

3. Inspect the loop. In the view or controller, find where a relation property is read for each model, e.g.:

   $posts = Post::find()->where(['status' => 1])->with('author')->limit(20)->all();
   
   foreach ($posts as $post) {
       echo $post->author->name;
   }

   Accessing $post->author inside the loop triggers a query per post when author is not eager loaded.

4. Confirm indexes. For the relation table, the foreign key column used to join back to the parent must be indexed. Check the schema for an index on author.id and post.author_id. Missing indexes make the IN(...) eager load slow.

Fixes tied to findings

Missing eager load

Replace lazy access with eager loading using with(). Yii fetches the parent rows first, then loads all related rows in one additional query using IN(...).

$posts = Post::find()->where(['status' => 1])->with('author')->limit(20)->all();

After the change, re-load the page and verify the debug panel shows one query for posts and one query for authors with an IN list of primary keys. Query count should stay bounded as page size grows.

Limitations: eager loading many or deep relations can produce large result sets. Over-eager loading rarely accessed relations can be slower than lazy loading.

Filtering or sorting by related columns

Use joinWith() when you need WHERE or ORDER BY on a related table. joinWith() performs a SQL JOIN.

$posts = Post::find()->joinWith('author')->where(['author.status' => 'active'])->all();

Risk: JOIN can duplicate parent rows. If pagination is used, the total count may be inflated. Mitigate with ->distinct() on the primary key or use a separate count query. joinWith() also loads the relation, so it behaves differently from Yii 1.x.

Reduce payload

For read-only lists, select only needed columns and use asArray() to avoid ActiveRecord hydration overhead.

$posts = Post::find()->select(['id','title','author_id'])->with(['author' => function($q){ $q->select(['id','name']); }])->asArray()->all();

For very large datasets, use batch() or each() to process in chunks and keep memory bounded.

Escalation criteria

If eager loading and proper indexes do not reduce latency:

• Profile the raw SQL with EXPLAIN in the database client. Run EXPLAIN on the eager loaded IN query to confirm the foreign key is used.
• Check for additional hidden queries from behaviors, access control, or widgets that fire per row.
• Consider denormalizing a frequently displayed attribute or adding a covering index.

Regression check: assert that query count stays under a fixed threshold when rendering N items as N grows. Enable the debug panel in a staging environment and compare before and after the with() change.

Note: with() and joinWith() behavior described here assumes Yii2/Yii3 style ActiveRecord. The debug toolbar counts are per request and may include queries unrelated to the controller.