Query optimization in Laravel - Step-by-Step Execution

Concept Flow - Query optimization

Write Eloquent Query

↓

Laravel Builds SQL

↓

Database Executes Query

↓

Check Query Performance

↓

Optimize Query

↓

Repeat Until Efficient

This flow shows how Laravel builds and runs a database query, then checks and improves its performance step-by-step.

Execution Sample

Laravel

use App\Models\User;

$users = User::where('active', 1)
    ->orderBy('created_at', 'desc')
    ->limit(5)
    ->get();

This code fetches the 5 most recently created active users from the database.

Execution Table

Step	Action	Laravel Query Builder State	Generated SQL	Database Action	Result
1	Start Eloquent Query	User model, where active=1	SELECT * FROM users WHERE active = 1	Prepare query	No data fetched yet
2	Add orderBy	Order by created_at DESC	SELECT * FROM users WHERE active = 1 ORDER BY created_at DESC	Prepare query	No data fetched yet
3	Add limit	Limit 5 rows	SELECT * FROM users WHERE active = 1 ORDER BY created_at DESC LIMIT 5	Prepare query	No data fetched yet
4	Execute get()	Final query ready	SELECT * FROM users WHERE active = 1 ORDER BY created_at DESC LIMIT 5	Run query	5 user records returned
5	Check performance	Query runs in 120ms	N/A	Analyze query plan	Index on active and created_at missing
6	Optimize query	Add index on active and created_at	N/A	Add DB index	Query runs faster
7	Re-run optimized query	Same query	SELECT * FROM users WHERE active = 1 ORDER BY created_at DESC LIMIT 5	Run query	5 user records returned in 20ms

💡 Query optimized by adding index; execution time reduced from 120ms to 20ms

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 7
$users	null	null	null	null	Collection of 5 users	Collection of 5 users (faster)
Query SQL	empty	WHERE active = 1	ORDER BY created_at DESC	LIMIT 5	Final SQL query	Final SQL query (same)
Execution Time	N/A	N/A	N/A	N/A	120ms	20ms

Key Moments - 3 Insights

Why does adding an index speed up the query?

Adding an index helps the database find matching rows faster, as shown in step 6 and 7 where execution time drops from 120ms to 20ms.

Why is the query not executed until get() is called?

Laravel builds the query step-by-step but delays running it until get() is called, as seen in steps 1-3 where no data is fetched yet.

What does orderBy do in the query?

orderBy sorts the results by a column; here it sorts users by created_at descending, shown in step 2 updating the SQL.

Visual Quiz - 3 Questions

Look at the execution table, what is the SQL query after step 3?

A SELECT * FROM users ORDER BY created_at DESC

B SELECT * FROM users WHERE active = 1 LIMIT 5

C SELECT * FROM users WHERE active = 1 ORDER BY created_at DESC LIMIT 5

D SELECT * FROM users

💡 Hint

Check the 'Generated SQL' column at step 3 in the execution table.

At which step does Laravel actually fetch data from the database?

A Step 2

B Step 4

C Step 6

D Step 1

💡 Hint

Look at the 'Database Action' and 'Result' columns to find when data is returned.

If we remove the limit(5), how would the execution table change?

A The SQL query would not have LIMIT clause at step 3

B The query would run faster

C The query would not have WHERE clause

D The query would order by ascending

💡 Hint

Compare the SQL query in step 3 with and without limit in the execution table.

Concept Snapshot

Laravel Query Optimization:
- Build queries step-by-step with Eloquent
- Query runs only when get() or similar is called
- Use orderBy, where, limit to control results
- Check query performance with DB tools
- Add indexes to speed up queries
- Repeat optimization until fast

Full Transcript

This visual trace shows how Laravel builds a database query using Eloquent. First, the query is constructed with conditions like where active=1, then ordered by creation date descending, and limited to 5 results. The SQL query is generated but not run until get() is called. The database executes the query and returns 5 user records. Performance is checked and found slow due to missing indexes. Adding an index on active and created_at columns speeds up the query significantly. The optimized query returns results much faster. Key points include that Laravel delays query execution until needed, indexes improve speed, and query parts like orderBy and limit shape the SQL. This step-by-step helps beginners see how query optimization works in Laravel.

Practice

1. What is the main benefit of using eager loading in Laravel queries?

easy

A. It reduces the number of database queries by loading related data upfront.

B. It automatically caches all query results for faster access.

C. It encrypts the query results for security.

D. It sorts the query results alphabetically.

Solution

1. Step 1: Understand eager loading purpose

   Eager loading loads related data in one query instead of many separate queries.

2. Step 2: Identify benefit in query optimization

   By reducing the number of queries, it speeds up the app and lowers database load.

3. Final Answer:

   It reduces the number of database queries by loading related data upfront. -> Option A

4. Quick Check:

   Eager loading = fewer queries [OK]

Hint: Eager loading means load related data once, not many times [OK]

Common Mistakes:

• Confusing eager loading with caching
• Thinking it sorts or encrypts data
• Believing it loads data lazily

2. Which of the following is the correct way to select only the 'name' and 'email' columns from the users table in Laravel?

easy

A. $users = DB::table('users')->select('name', 'email')->get();

B. $users = DB::table('users')->columns('name', 'email')->fetch();

C. $users = DB::table('users')->only('name', 'email')->all();

D. $users = DB::table('users')->pick('name', 'email')->fetchAll();

Solution

1. Step 1: Recall Laravel query builder syntax

   The method to specify columns is select(), and to get results is get().

2. Step 2: Match correct syntax

   $users = DB::table('users')->select('name', 'email')->get(); uses select('name', 'email')->get(), which is correct Laravel syntax.

3. Final Answer:

   $users = DB::table('users')->select('name', 'email')->get(); -> Option A

4. Quick Check:

   Select columns = select() + get() [OK]

Hint: Use select() to pick columns, then get() to fetch [OK]

Common Mistakes:

• Using non-existent methods like columns() or only()
• Confusing fetch() with get()
• Trying to fetch all columns without select()

3. Given the code below, what will be the number of queries executed?

Post::with('comments')->where('status', 'published')->get();

medium

A. 1 query

B. Number of comments queries

C. Number of posts + 1 queries

D. 2 queries

Solution

1. Step 1: Understand eager loading with 'with'

   The with('comments') loads related comments in a separate query.

2. Step 2: Count queries executed

   One query fetches posts with status 'published', second query fetches all related comments.

3. Final Answer:

   2 queries -> Option D

4. Quick Check:

   with() = 2 queries [OK]

Hint: with() runs 2 queries: main + related data [OK]

Common Mistakes:

• Thinking with() runs only 1 query
• Assuming one query per post for comments
• Confusing with() with lazy loading

4. Identify the problem in this Laravel query that causes slow performance:

$users = User::all();
foreach ($users as $user) {
  echo $user->profile->bio;
}

medium

A. The profile relation does not exist in User model.

B. The foreach loop syntax is incorrect.

C. Using all() loads all users without eager loading profiles, causing N+1 queries.

D. Using echo inside loop slows down PHP execution.

Solution

1. Step 1: Analyze query and loop

   User::all() loads all users but does not load related profiles eagerly.

2. Step 2: Identify N+1 query problem

   Accessing $user->profile inside loop triggers one query per user, causing many queries.

3. Final Answer:

   Using all() loads all users without eager loading profiles, causing N+1 queries. -> Option C

4. Quick Check:

   all() without eager loading = N+1 queries [OK]

Hint: Use with('profile') to avoid N+1 queries [OK]

Common Mistakes:

• Blaming loop syntax instead of query
• Assuming profile relation missing without checking
• Thinking echo slows query performance

5. You want to optimize a query that fetches posts with their authors and comments, but only need the post title, author name, and comment content. Which approach is best?

hard

A. Run separate queries for posts, authors, and comments and merge results in PHP.

B. Use eager loading with select() on posts, authors, and comments to fetch only needed columns.

C. Use lazy loading to fetch authors and comments only when accessed.

D. Fetch all posts, authors, and comments without filtering columns to avoid missing data.

Solution

1. Step 1: Understand data needed

   Only post title, author name, and comment content are required, so select only these columns.

2. Step 2: Apply eager loading with column selection

   Use with() to eager load authors and comments, and select() to limit columns for optimization.

3. Final Answer:

   Use eager loading with select() on posts, authors, and comments to fetch only needed columns. -> Option B

4. Quick Check:

   Eager load + select columns = best optimization [OK]

Hint: Combine eager loading and select() to fetch only needed data [OK]

Common Mistakes:

• Fetching all columns wastes resources
• Using lazy loading causes many queries
• Merging in PHP is slower than optimized queries