We've all been there.
An API that used to respond in milliseconds suddenly becomes slower as the database grows. The application logic hasn't changed much, but performance keeps degrading.
In many cases, the problem isn't the framework or the programming language—it's how we're interacting with the database.
This article shares a few practical optimization techniques I've used while working on backend systems. These aren't advanced tricks; they're small improvements that can make a noticeable difference in production environments.
1. Don't Apply Functions to Indexed Columns
Imagine you have an indexed column named updated.
A common approach is filtering records like this:
WHERE DATE(updated) = '2026-07-30'
Although it works, there's a hidden cost.
Because updated is wrapped inside a function, the database usually can't use the index efficiently. Instead of performing an index range scan, it may need to evaluate every row.
A better approach is to compare the raw timestamp:
WHERE updated >= '2026-07-30 00:00:00'
AND updated < '2026-07-31 00:00:00'
This allows the database optimizer to use the index effectively.
2. Convert Input Instead of Every Database Row
I recently reviewed code that converted every timestamp inside SQL using time zone functions.
While technically correct, it forced the database to perform a conversion for every matching row.
Instead, convert the user's input once in your application:
$fromLocal->setTimezone(new DateTimeZone('UTC'));
Then execute a simple query:
WHERE updated >= ?
AND updated < ?
The result is easier to read, more maintainable, and usually much faster.
3. Don't Load Thousands of Rows Just to Count Them
I've seen code similar to this:
$total = count($Model->query($sql));
It works, but think about what's happening.
The database sends every matching row to your application.
Your application allocates memory for every record.
Finally, PHP counts the array.
If the query returns 200,000 rows, you've transferred and stored 200,000 records simply to obtain a number.
Instead, let the database do the counting:
SELECT COUNT(*)
FROM ...
Or, if you're wrapping an existing query:
SELECT COUNT(*)
FROM (
...
) results;
The database returns a single integer instead of thousands of records.
4. Write Queries That Scale
A query that performs well with 500 rows may become a bottleneck when your database reaches millions of records.
Whenever you review a query, ask yourself:
- Can this use an index?
- Am I processing unnecessary data?
- Can the database perform this operation more efficiently than my application?
- Am I transferring more data than I actually need?
These questions often reveal opportunities for optimization.
5. Readability Is Also an Optimization
Performance isn't the only goal.
Code that is easy to understand is easier to maintain, debug, and improve.
For example, converting user input once in the application layer is often simpler than embedding multiple conditional time zone conversions inside SQL.
Future developers—including your future self—will thank you.
Final Thoughts
Database optimization doesn't always require rewriting your architecture.
Often, the biggest gains come from small improvements:
- Avoid wrapping indexed columns in functions.
- Convert user input instead of every database row.
- Use
COUNT(*)when you only need totals. - Return only the data you actually need.
- Think about how the database executes your query—not just whether the query works.
As your application grows, these habits become increasingly valuable. Small optimizations applied to frequently executed queries can have a meaningful impact on performance, resource usage, and overall scalability.
What optimization technique has had the biggest impact on your applications? I'd love to hear your experiences in the comments.
Top comments (0)