When a web application feels slow, the database is the usual suspect — and the usual mistake is to start optimizing before measuring. The slowest query in a system is almost never the one anyone guesses. So this list is ordered the way we actually work through a client’s performance problem, and it starts with evidence.
1. Measure first — the slow query log is free
Both MySQL and PostgreSQL will log every query slower than a threshold you set. Turn that on, let it collect a day of real traffic, and you have a ranked to-do list instead of a hunch. For the candidates it surfaces, EXPLAIN (ANALYZE in Postgres) shows what the database actually does — the full table scan hiding behind an innocent-looking filter, the join order you did not expect. Application-side monitoring earns its keep here too, because it ties slow queries to the screens and jobs that run them.
2. Indexes that match real queries
Indexing is not "add an index per column". An index helps when it matches the shape of a query: a query filtering on customer and ordering by date wants one composite index on both, in that order — not two separate ones. Column order in composite indexes matters; equality filters go before ranges. And every index taxes every write, so a table taking thousands of inserts a minute should carry exactly the indexes it needs and no souvenirs. We regularly find tables where half the indexes have never been used once — both major databases will tell you this if you ask.
3. Kill the N+1 queries
The most common performance bug in ORM-based applications, Laravel’s Eloquent included: load fifty orders, then lazily load each order’s customer — fifty-one queries where two would do. It is invisible in development with ten test records and devastating with fifty thousand real ones. The fix is eager loading the relations you know the page needs; the discipline is detection — query counters in development tooling, and a hard look at any endpoint whose query count scales with its row count.
4. Rewrite the query before scaling the server
Three rewrites pay for themselves constantly. Select only the columns you use — dragging a megabyte of text columns through memory to render a list of titles is pure waste. Replace OFFSET pagination on large tables with keyset pagination (filter on "after id/date" instead of skipping rows) — OFFSET 200000 reads and discards two hundred thousand rows, which is why deep pages die. And push aggregation into the database: a SUM over indexed rows beats fetching them all and adding in application code, every time.
5. Cache honestly
Caching is the technique everyone reaches for first and should usually reach for last — a cache layered over a slow query hides it until the moment of invalidation, then returns it with interest. What earns a cache in our systems: expensive aggregates (dashboard totals), reference data that changes rarely, and rendered fragments of catalog pages. What does not: anything entangled with per-user permissions, and anything you cannot confidently invalidate. If you cannot say in one sentence when a cached value becomes wrong, do not cache it.
6. Schema decisions you will live with for years
Some database pain is structural. JSON columns are wonderful for genuinely variable attributes and a trap when you start filtering and joining on what is inside them — fields you query belong in real columns. Soft-deletes accumulate: after a few years, a third of a table can be rows nobody will ever look at, scanned by every query; archive tables are unglamorous and effective. The same goes for history — audit logs and event tables grow without limit unless something is allowed to expire or move to cold storage.
7. Connections and infrastructure — last, not first
Connection pooling matters once traffic is real — establishing connections per-request burns time and, in Postgres, memory per connection (a pooler like PgBouncer is standard kit). Read replicas help genuinely read-heavy workloads, at the price of replication-lag awareness in the application. And sometimes the honest answer is hardware — databases love RAM — but buying a bigger server to outrun an N+1 bug is renting a solution to an owned problem, which is why this section is last.
8. Sometimes the answer is not optimization
The fastest query is one you stop running. Does the dashboard truly need live totals over ten million historical rows, or is a nightly precomputed figure just as useful? Can data older than three years move to an archive? Some of the most effective "optimization" work we have done was redefining a feature with the client — ten minutes of product conversation outperforming a week of tuning.
Where this comes from
This is the unglamorous layer of the systems we build and maintain — e-commerce platforms with large catalogs, CRM and ERP systems carrying years of operational records. If your application has grown slower as its data has grown bigger, that is one of the most fixable problems in our trade — get in touch and we will start with the measurements, not the invoice.