Database optimisation for web applications: measure first, then fix
A quote page that shows nothing for five seconds. A dashboard that stalls every morning around nine. A webshop that runs fine for months, until a campaign doubles the traffic. Ask three people what causes it and you get three answers: the server, the theme, the visitor's internet connection. In practice the time usually disappears inside the database, and that is the layer people check last.
A database does not have to be big to become slow. One unnecessary query on a busy page, a missing index, an integration that fetches the customer record again for every order line: any of those can be enough. Targeted optimisation therefore often delivers a noticeable gain without rebuilding anything. The condition is that you first measure where the delay actually starts, because the place where you feel it is rarely the place where it begins.
When database performance becomes a business problem
Users never experience a slow database as a database problem. They see a search function that keeps spinning, duplicate rows in an overview, an error at checkout or an order that does not come through. Internally your team notices that reports take minutes, that colleagues sit waiting on a screen and that support tickets pile up. That costs time, trust and sometimes revenue.
In web applications the load also grows unnoticed. New features use extra tables, integrations add synchronisations and historical data simply stays because nobody dares to clean it up. None of that is wrong in itself. It only becomes a problem when the technical setup does not grow along with the usage.
Watch for these signals in particular:
- Certain pages or API calls are structurally slow at fixed moments.
- Database load peaks while visitor numbers stay flat.
- Searching, filtering and reporting get noticeably slower as the data grows.
- Time-outs or lock messages appear during imports, payments or synchronisations.
Not every signal calls for the same fix. A slow report may be missing an index, but it may just as easily be a report that does not belong in the transactional database at all. A spike in load can come from sloppy code, but also from an environment that is simply too tightly sized. Guessing is almost always more expensive than measuring.
Database optimisation for web applications starts with insight
The first step is making the real load visible. Which queries cost the most time in total? How often do they run? Which tables grow fastest? And does the delay originate in the database itself, in the application code, in an external API or in the hosting layer?
Do not look only at the slowest query. A query of 100 milliseconds that runs ten thousand times an hour does more damage than a two second report query that runs once a night. The total adds up to more than the top score.
Judge that behaviour on production as well. A test environment rarely has the same amount of data, the same number of concurrent users and the same background processes as the live environment. Optimising against a clean test database of five hundred records produces fixes that still fall over once they go live.
Look at patterns, not at individual queries
The classic example is the N+1 query. The application first fetches a hundred orders and then fires a separate query for every single order to get the customer details or the order lines. With ten records nobody notices. With a thousand you suddenly need a thousand queries for one page.
Wide selections deserve attention too. If an overview only shows a name, a status and a date, it does not need to pull in every field and every linked table. Less data means less processing, less memory use and usually a faster response.
The same goes for filters and sorting. A search screen where someone can filter on customer, status, period and staff member is genuinely useful. Without suitable indexes that same screen makes the database walk through the entire table once the dataset is large. Functionally everything still works. Workable it is not.
Indexes help, but they are not a miracle cure
An index works roughly like the index at the back of a book. The database does not have to read every page to find what you are looking for. For searches, joins and sorting it is often the fastest gain available.
More indexes is not automatically better, though. Every index takes up space and has to be updated as soon as a record is added or changed. A table with ten indexes reads fast and writes slowly. In an environment with many orders, stock movements or log entries that trade-off matters.
The right index follows the usage pattern. If people almost always search on organisation and status together, one composite index is usually more useful than two separate ones. If a date filter is only used at night by a report, it can sit lower on the list than the filter on the customer page that stays open all day. This is craftsmanship, not a checklist you tick off blindly.
Keep transactions short and avoid blocking
Web applications rarely do one thing at a time. While a customer places an order, a staff member adjusts stock and an integration sends data to the accounting package. If one process holds on to the same records for a long time, the others have to wait. That is called locking, and to the user it feels like slowness or an unexplained error message.
Short transactions are the best defence. Do not call an external API while a transaction is open. Process large imports in manageable batches. And do not let background jobs update tens of thousands of records in the middle of the day, unrestrained, while colleagues and customers need those same records.
Which approach is best depends on the process. Stock and financial data sometimes demand strict consistency, and then speed is not the only standard. For notifications, exports or reports, processing can happily happen a few seconds later. The skill lies in that distinction: which data has to be correct at that exact moment, and which action can safely wait?
Separate daily operations from heavy reporting
Many applications use one database for everything: the website, the customer portal, the integrations, the exports and the management reports. As a starting point that is fine. As an end state it starts to chafe. A report covering five years of history can noticeably affect the experience of active users, and that tends to happen at the busiest moment of the week.
Sometimes optimisation inside the same database is enough: better queries, targeted indexes and heavy processing scheduled outside peak hours. Sometimes a separate reporting environment, a caching layer or a read replica is the better fit. That adds management and complexity, but it does keep the primary application out of the wind.
Be level headed about caching. Data that has to be correct at any given moment, such as current stock or a payment status, should not be cached aggressively. Frequently read data that rarely changes, such as product categories, settings or public content, makes a good candidate. Caching does not repair a poor data model, but it does remove a lot of repeat traffic.
The hosting layer is part of the picture
A perfectly written query still depends on the environment it runs in. Too little memory, slow storage, badly configured database parameters or other processes draining the same machine all slow things down. The other way round, a bigger server does not fix an inefficient query, it just hides it for a while. Application, database and infrastructure therefore have to be judged together.
That is where fragmented management hurts. If the developer points at the hosting and the host points at the code, the problem stays where it is and you pay for two investigations without an outcome. A technical partner who knows both the application and the infrastructure can measure, adjust and check straight away whether the change actually made a difference. At LJPc, development and hosting are not separate counters for that reason.
Monitoring is not a luxury either. Track response times, database load, error messages, slow queries, storage growth and capacity over a longer period. A one off optimisation round is sometimes necessary, but for a growing application the real gain is in spotting things early. Then you plan an improvement before a busy month, a new client or a marketing push turns it into an incident.
Make performance part of your change process
New functionality is usually what creates new load. An extra filter, a broader export or an integration looks small but can add a mountain of database work. So include performance in the review before anything goes live. Which data gets read and written? How often does that happen? And what happens with ten times as many records or concurrent users?
That barely slows development down. Mostly it saves emergency work afterwards. Test important changes against a realistic dataset and realistic load, and the bottlenecks surface at a point where you can still solve them calmly.
The most useful next step is a small one. Pick one process where colleagues or customers are genuinely losing time, measure the full chain from click to answer, and tackle the biggest cause first. Not every millisecond deserves attention. Every delay in a process your business runs on does.