Select queries in Laravel - Performance & Optimization

Performance: Select queries

HIGH IMPACT

Select queries affect how fast data is fetched from the database and how quickly the page can show content.

Fetching all columns from a large table without filtering

Laravel

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

Only fetches needed columns, reducing data size and speeding up query.

📈 Performance GainReduces data transfer size and speeds up LCP by loading less data.

Fetching all columns from a large table without filtering

Laravel

$users = DB::table('users')->get();

This fetches all columns and all rows, which can be very large and slow down response time.

📉 Performance CostBlocks rendering until all data is fetched; can add hundreds of KBs to response size.

Performance Comparison

Pattern	DOM Operations	Reflows	Paint Cost	Verdict
Select all columns without filter	N/A	N/A	High data transfer delays paint	[X] Bad
Select specific columns	N/A	N/A	Less data speeds paint	[OK] Good
N queries in loop	N/A	N/A	Multiple DB hits delay response	[X] Bad
Eager loading related data	N/A	N/A	Fewer queries speed paint	[OK] Good
Raw queries without caching	N/A	N/A	Repeated slow queries delay paint	[X] Bad
Cached query results	N/A	N/A	Faster repeated loads	[OK] Good

Rendering Pipeline

Select queries impact the server response time, which delays when the browser can start rendering the page content.

→Server Processing

→Network Transfer

→Rendering

⚠️ BottleneckServer Processing (database query execution)

Core Web Vital Affected

LCP

Select queries affect how fast data is fetched from the database and how quickly the page can show content.

Optimization Tips

1Select only the columns you need to reduce data size.

2Avoid running queries inside loops; use eager loading instead.

3Cache query results to speed up repeated requests.

Performance Quiz - 3 Questions

What is a performance benefit of selecting only needed columns in a Laravel query?

A Increases database CPU usage

B Reduces data size sent over network, speeding up page load

C Triggers more reflows in the browser

D Blocks JavaScript execution

DevTools: Network

How to check: Open DevTools, go to Network tab, reload page, and look at the timing of API or page requests that run select queries.

What to look for: Long server response times or large payload sizes indicate slow or heavy select queries.

Practice

1. What does the select() method do in a Laravel query builder?

easy

A. It specifies which columns to retrieve from the database.

B. It inserts new data into the database.

C. It deletes rows from the database table.

D. It updates existing records in the database.

Solution

1. Step 1: Understand the purpose of select()

   The select() method tells Laravel which columns you want to get from the database table.

2. Step 2: Compare with other query methods

   Methods like insert(), delete(), and update() perform different actions, not selecting columns.

3. Final Answer:

   It specifies which columns to retrieve from the database. -> Option A

4. Quick Check:

   select() chooses columns [OK]

Hint: Remember: select() picks columns, not rows or actions [OK]

Common Mistakes:

• Confusing select() with insert() or update()
• Thinking select() deletes data
• Assuming select() fetches all columns by default

2. Which of the following is the correct syntax to select the columns 'name' and 'email' from the 'users' table using Laravel's query builder?

easy

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

B. DB::table('users')->select('name' & 'email')->get();

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

D. DB::table('users')->select('name' + 'email')->get();

Solution

1. Step 1: Check the correct way to pass multiple columns

   Laravel's select() accepts an array of column names to select multiple columns.

2. Step 2: Analyze each option's syntax

   DB::table('users')->select('name, email')->get(); uses select('name, email'), which selects a single column named 'name, email'. Options B and D use invalid operators & and +. DB::table('users')->select(['name', 'email'])->get(); correctly uses an array ['name', 'email'].

3. Final Answer:

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

4. Quick Check:

   Use array for multiple columns in select() [OK]

Hint: Use array syntax for multiple columns in select() [OK]

Common Mistakes:

• Passing multiple strings without array
• Using operators like + or & inside select()
• Forgetting to call get() to execute query

3. What will be the output of the following Laravel query?

DB::table('products')->select('name')->where('price', '>', 100)->get();

medium

A. All products with price greater than 100, showing all columns.

B. All products with price less than or equal to 100, showing only their names.

C. All products with price greater than 100, showing only their names.

D. Syntax error due to incorrect where clause.

Solution

1. Step 1: Understand the where condition

   The query filters products where the price is greater than 100.

2. Step 2: Understand the select clause

   Only the 'name' column is selected, so only product names will be returned.

3. Final Answer:

   All products with price greater than 100, showing only their names. -> Option C

4. Quick Check:

   where('price', '>', 100) filters price > 100 [OK]

Hint: where() filters rows; select() chooses columns [OK]

Common Mistakes:

• Confusing > with < in where clause
• Expecting all columns instead of selected ones
• Thinking where() causes syntax error here

4. Identify the error in this Laravel query:

DB::table('orders')->select('id', 'total')->where('status' = 'pending')->get();

medium

A. Using '=' instead of '==' in where clause.

B. Using '=' instead of ',' to separate arguments in where().

C. Missing semicolon at the end of the query.

D. select() cannot take multiple columns.

Solution

1. Step 1: Check the where clause syntax

   The where method expects two arguments separated by a comma, not an assignment with '='.

2. Step 2: Identify the correct syntax

   The correct syntax is where('status', 'pending'), not where('status' = 'pending').

3. Final Answer:

   Using '=' instead of ',' to separate arguments in where(). -> Option B

4. Quick Check:

   where() needs comma between arguments [OK]

Hint: Use comma, not =, between where() arguments [OK]

Common Mistakes:

• Using = instead of comma in where()
• Confusing assignment with comparison
• Thinking select() can't take multiple columns

5. You want to get the 'id' and 'email' of users who are active and have verified their email. Which Laravel query correctly does this?

hard

A. DB::table('users')->select(['id', 'email'])->where('active' = 1)->where('email_verified' = 1)->get();

B. DB::table('users')->select('id', 'email')->where('active' == 1 && 'email_verified' == 1)->get();

C. DB::table('users')->select(['id', 'email'])->where('active', '=', 1, 'email_verified', '=', 1)->get();

D. DB::table('users')->select(['id', 'email'])->where(['active' => 1, 'email_verified' => 1])->get();

Solution

1. Step 1: Understand filtering multiple conditions

   Laravel allows passing an associative array to where() to filter multiple columns with AND logic.

2. Step 2: Analyze each option

   DB::table('users')->select(['id', 'email'])->where(['active' => 1, 'email_verified' => 1])->get(); correctly uses where(['active' => 1, 'email_verified' => 1]). DB::table('users')->select('id', 'email')->where('active' == 1 && 'email_verified' == 1)->get(); uses invalid syntax with == and && inside where(). DB::table('users')->select(['id', 'email'])->where('active', '=', 1, 'email_verified', '=', 1)->get(); passes too many arguments to a single where(). DB::table('users')->select(['id', 'email'])->where('active' = 1)->where('email_verified' = 1)->get(); uses = instead of , to separate where() arguments, causing syntax error.

3. Final Answer:

   DB::table('users')->select(['id', 'email'])->where(['active' => 1, 'email_verified' => 1])->get(); -> Option D

4. Quick Check:

   Use array in where() for multiple AND conditions [OK]

Hint: Use array in where() for multiple AND filters [OK]

Common Mistakes:

• Using invalid operators inside where()
• Passing too many arguments to where()
• Not using array for multiple conditions