One of the most common questions I hear is:
“My API is slow. Where do I start?”
The first instinct is usually:
- Upgrade the server
- Increase CPU
- Add more RAM
But in many cases, the database query is the real bottleneck.
Whenever I investigate a slow Laravel application, I follow the same checklist. It helps me identify performance issues before making unnecessary infrastructure changes.
Let’s go through it.
1️⃣ Find the Slow Queries First
Don’t start optimizing random queries.
Start with the queries that are executed the most or take the most time.
Useful tools:
- Laravel Telescope
- Laravel Debugbar (development)
- MySQL Slow Query Log
- Application Performance Monitoring (APM)
You can’t optimize what you haven’t measured.
2️⃣ Stop Using SELECT *
One of the easiest improvements.
❌ Instead of:
SELECT *
FROM users
WHERE id = 10;
Enter fullscreen mode Exit fullscreen mode
Use:
SELECT id, name, email
FROM users
WHERE id = 10;
Enter fullscreen mode Exit fullscreen mode
Why?
- Less data transferred
- Lower memory usage
- Faster response
- Easier for MySQL to use covering indexes
Only fetch the columns your application actually needs.
3️⃣ Always Check the Execution Plan
Before changing anything, run:
EXPLAIN
SELECT id, name
FROM users
WHERE email = '[email protected]';
Enter fullscreen mode Exit fullscreen mode
Things I usually look for:
- Is MySQL scanning the whole table?
- Is an index being used?
- How many rows are examined?
- Is there a temporary table?
- Is filesort being used?
EXPLAIN often tells you exactly why a query is slow.
4️⃣ Verify Your Indexes
Indexes are one of the biggest performance improvements you can make—but only when they match your queries.
Example:
SELECT *
FROM orders
WHERE customer_id = 100;
Enter fullscreen mode Exit fullscreen mode
Create an index:
CREATE INDEX idx_customer_id
ON orders(customer_id);
Enter fullscreen mode Exit fullscreen mode
Now MySQL can jump directly to the matching rows instead of scanning the entire table.
5️⃣ Look for Composite Index Opportunities
Suppose your query is:
SELECT id, total
FROM orders
WHERE customer_id = 10
AND status = 'paid';
Enter fullscreen mode Exit fullscreen mode
Instead of two separate indexes:
customer_id
status
Enter fullscreen mode Exit fullscreen mode
A composite index is often better:
CREATE INDEX idx_customer_status
ON orders(customer_id, status);
Enter fullscreen mode Exit fullscreen mode
Remember:
The order of columns inside a composite index matters.
6️⃣ Avoid Functions in the WHERE Clause
This prevents MySQL from using indexes efficiently.
❌ Bad:
SELECT *
FROM users
WHERE YEAR(created_at) = 2026;
Enter fullscreen mode Exit fullscreen mode
Better:
SELECT *
FROM users
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';
Enter fullscreen mode Exit fullscreen mode
Now MySQL can use an index on created_at.
7️⃣ Watch for N+1 Queries
This is one of the most common Laravel performance problems.
❌ Example:
$users = User::all();
foreach ($users as $user) {
echo $user->posts;
}
Enter fullscreen mode Exit fullscreen mode
This may execute:
- 1 query for users
- N queries for posts
Instead, eager load the relationship:
$users = User::with('posts')->get();
Enter fullscreen mode Exit fullscreen mode
Much fewer database queries.
8️⃣ Cache Frequently Used Data
Not every request needs to hit the database.
For data that changes infrequently, caching can dramatically reduce database load.
Example with Laravel:
$users = Cache::remember(
'users',
300,
fn () => User::all()
);
Enter fullscreen mode Exit fullscreen mode
The first request reads from the database.
Subsequent requests are served from the cache until it expires.
9️⃣ Add Pagination
Fetching thousands of rows at once is rarely necessary.
Instead of:
User::all();
Enter fullscreen mode Exit fullscreen mode
Use:
User::paginate(20);
Enter fullscreen mode Exit fullscreen mode
Benefits:
- Faster queries
- Smaller responses
- Better user experience
🔟 Measure Again
Optimization isn’t finished after adding an index.
Measure the results.
Compare:
- Query execution time
- Number of rows scanned
- API response time
- CPU usage
- Database load
Always verify that your changes actually improved performance.
My Personal Optimization Workflow
Slow API
↓
Identify the slow query
↓
Run EXPLAIN
↓
Check indexes
↓
Remove SELECT *
↓
Look for N+1 queries
↓
Cache frequently accessed data
↓
Measure again
Enter fullscreen mode Exit fullscreen mode
Final Thoughts
Performance optimization isn’t about applying every trick you know.
It’s about understanding why a query is slow and making targeted improvements.
Most of the biggest gains I’ve seen came from simple changes like:
- Selecting only the required columns
- Adding the right index
- Eliminating N+1 queries
- Caching frequently requested data
Small optimizations, applied consistently, can have a huge impact on application performance.
What are your go-to techniques for optimizing slow MySQL queries? I’d love to hear your approach in the comments.
답글 남기기