Why Optimization Matters
A slow database query can bring down an entire application under load. These 10 techniques will dramatically improve performance.
1. Use EXPLAIN
hljs sql
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
Look for type: ALL (full table scan) — that needs an
index.
2. Add the Right Indexes
hljs sql
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at DESC);
- ▸Avoid SELECT *
hljs sql
-- BAD
SELECT * FROM users WHERE id = 1;
-- GOOD
SELECT id, name, email FROM users WHERE id = 1;
4. Fix N+1 in
Laravel
hljs php
// BAD - N+1
$posts = Post::all();
foreach ($posts as $post) {
echo $post->author->name; // query per post
}
// GOOD - eager load
$posts =
Post::with('author')->get();
5. Use Query Caching
hljs php
$users = Cache::remember('active_users', 3600, function () {
return User::where('active',
true)->get();
});
These patterns reduced query time by 80% on a 2M-row table in production.
