When an application becomes slow, developers often look at the frontend first.
Maybe the React component is rendering too many times.
Maybe the API is slow.
Maybe the server needs more CPU.
But sometimes the real problem is much simpler:
Your database is doing way more work than necessary.
And the scary part?
Your application can work perfectly while still making terrible database queries.
Here are some common mistakes I've started paying much more attention to.
1. Stop Using SELECT * Everywhere
This is probably one of the easiest things to overlook.
Instead of:
SELECT * FROM users;
ask for only what you actually need:
SELECT id, name, email FROM users;
Why?
Imagine your users table contains:
- profile images
- addresses
- preferences
- metadata
- timestamps
- large JSON fields
If you only need the user's name and email, fetching everything is unnecessary work.
Simple rule:
Fetch what you need, not everything available.
2. Indexes Can Change Everything
Imagine you have a table containing millions of users.
You frequently search by email:
SELECT * FROM users
WHERE email = 'user@example.com';
Without an appropriate index, the database may need to inspect a huge number of rows.
An index can make this dramatically more efficient:
CREATE INDEX idx_users_email
ON users(email);
But there's an important catch.
Don't index every column.
Indexes also consume storage and add overhead when rows are inserted or updated.
Index columns that you frequently:
- search
- filter
- sort
- join
And verify your assumptions with query plans.
For example:
EXPLAIN SELECT *
FROM users
WHERE email = 'user@example.com';
3. Beware of the N+1 Query Problem
This one can quietly destroy performance.
Imagine you fetch 100 posts:
Get 100 posts
Then your application separately asks the database for the author of each post:
Get author 1
Get author 2
Get author 3
...
Get author 100
Now you've potentially made:
101 database queries.
Instead of doing that, use a proper join or ORM relation loading strategy.
For example:
SELECT posts.title, users.name
FROM posts
JOIN users
ON posts.user_id = users.id;
One query can often replace dozens or hundreds of database calls.
4. Don't Query the Same Data Repeatedly
Sometimes the database isn't slow.
We're just asking it the same question again and again.
For example:
Request → Database
Request → Database
Request → Database
Request → Database
If the data doesn't change frequently, caching can help.
A simplified architecture might look like:
Client
↓
API
↓
Cache
↓
Database
The application checks the cache first.
If the data exists there, the database doesn't need to do the work again.
Tools such as Redis are commonly used for this kind of caching.
But remember:
Caching is not a replacement for fixing bad queries.
5. Pagination Is Not Optional at Scale
Imagine your API returns every user:
SELECT * FROM users;
That might work when you have 100 users.
What happens when you have:
10 million users?
Your database has to retrieve a massive amount of data, and your server then has to process and send it.
Instead, paginate:
SELECT id, name, email
FROM users
LIMIT 20 OFFSET 0;
Then:
Page 1 → 20 records
Page 2 → 20 records
Page 3 → 20 records
For very large datasets, cursor-based pagination can be more efficient than large offsets.
6. Don't Optimize Blindly
One of the biggest lessons in performance optimization:
Don't guess. Measure.
Before changing your query, find out what the database is actually doing.
Use tools such as:
EXPLAIN
or:
EXPLAIN ANALYZE
Depending on your database.
Look for things like:
- Full table scans
- Missing indexes
- Expensive joins
- Sorting large datasets
- Excessive rows being examined
- Slow execution time
Optimization becomes much easier when you can see the actual problem.
A Simple Performance Checklist 🚀
Before blaming your backend for a slow application, ask:
- [ ] Am I fetching unnecessary columns?
- [ ] Do frequently searched columns have appropriate indexes?
- [ ] Am I accidentally creating N+1 queries?
- [ ] Am I making the same query repeatedly?
- [ ] Am I returning too many records?
- [ ] Do I need pagination?
- [ ] Have I checked the query execution plan?
- [ ] Am I optimizing based on measurements rather than assumptions?
The Bigger Lesson
Database performance isn't always about buying a bigger server.
Sometimes the biggest improvement comes from simply asking the database to do less work.
A query that takes 2 seconds might become 200ms.
A query that runs 100 times might become one query.
A response containing 50 unnecessary fields might become 5 useful ones.
That's why database optimization often starts with a surprisingly simple question:
“Does the database really need to do all of this?”
Before adding more infrastructure, check the query.
You might already have enough power.
You just might be using it inefficiently. ⚡
What database optimization trick has saved you the most performance?
I'd love to hear what you've encountered in your own projects. 👇
Top comments (0)