Query optimization in Laravel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is query optimization in Laravel?

Query optimization in Laravel means making database queries faster and use fewer resources by writing efficient code and using Laravel features well.

beginner

How does eager loading improve query performance in Laravel?

Eager loading loads related data in one query instead of many, reducing the number of database calls and speeding up the app.

intermediate

What is the difference between 'get()' and 'pluck()' in Laravel queries?

'get()' fetches full records, while 'pluck()' fetches only one column’s values, making 'pluck()' faster when you need less data.

beginner

Why should you avoid using 'select *' in Laravel queries?

Using 'select *' fetches all columns, which can slow down queries and use more memory. Selecting only needed columns is faster.

intermediate

How can indexing database columns help Laravel query optimization?

Indexing creates a quick lookup for columns used in searches or joins, making queries faster by reducing the data scanned.

Which Laravel feature helps reduce the number of database queries when loading related models?

A Eager loading

B Lazy loading

C Query builder

D Pagination

Eager loading loads related models in one query, reducing the total queries made.

What does the 'pluck()' method do in Laravel queries?

A Fetches all columns of a record

B Updates records

C Fetches a single column's values

D Deletes records

'pluck()' retrieves values of a single column, which is faster when you don't need full records.

Why is it better to select specific columns instead of using 'select *' in Laravel?

A It makes queries slower

B It disables caching

C It causes errors

D It fetches unnecessary data

Selecting specific columns avoids fetching extra data, improving speed and memory use.

What is the main benefit of adding indexes to database columns used in Laravel queries?

A Slows down inserts

B Speeds up searches and joins

C Increases storage size

D Prevents data duplication

Indexes help the database find data faster during searches and joins.

Which method in Laravel helps to limit the number of records returned by a query?

A limit()

B pluck()

C join()

D orderBy()

'limit()' restricts how many records the query returns, improving performance.

Explain how eager loading works in Laravel and why it improves query performance.

Think about how loading related data in one go helps.

Describe three ways to optimize database queries in Laravel.

Consider methods and database techniques.

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