padosoft-query-performancelisted

# Query performance Every rule here is about the same thing: **the code was written against the data volume that existed when it was written**. It worked. The volume moved. --- ## 1. Never query inside a loop | Need | Instead of a query per iteration | |---|---| | Check whether records exist | pluck the ids once, check membership in memory | | Load a relation | eager-load it **outside** the loop | | Count per group | one grouped aggregate query | | Fetch by a list of ids | one `whereIn`, keyed by id | | Insert many rows | collect and insert in batches — note that a bulk insert skips ORM events and timestamps | A loop of five queries is fine in development and is five thousand in production. ## 2. Select the columns you need ```php // ❌ every column, including the text blobs nobody asked for Article::query()->where('brand_id', $id)->get(); // ✅ Article::query()->select(['id', 'name', 'price'])->where('brand_id', $id)->get(); ``` Same inside eager loads — **and include the primary key and the foreign key**, or the relation cannot be matched back and silently comes out empty: ```php ->with(['prices:id,article_id,price,discounted_price']) ``` ## 3. The volume decides the technique | Rows | Technique | When | |---|---|---| | under ~1 000 | load it all | it fits in memory | | 1 000 – 100 000 | chunk with a callback | batch processing | | 1 000 – 100 000, stable ids | **chunk by id** | cron and queued jobs — no offset drift when rows are modified mid-walk | | any, read-on