Why your site slows down as the database grows

A classic pattern: a site is fast for the first few months, then gradually slows down without a single line of code changing. The most common cause is simple: the database has grown, and queries that were fast on little data become slow on a lot of data. The good news is that the diagnosis is methodical and the fix usually does not require rebuilding anything.

Why a query slows down with volume

A query that has to scan an entire table to find a few rows (for example, looking up a customer by email with no index on that column) takes time roughly proportional to the number of rows. With a hundred rows, the difference is invisible; with a hundred thousand, it becomes noticeable; with a million, it can make a page unusable.

Find the slow queries

Before fixing anything, you need to know what to fix. MySQL and MariaDB can record every query that exceeds a given duration, in the slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- in seconds

After a few hours of normal use, this log lists the culprits, with how often they run. On the application side, tools such as Laravel Debugbar, the Symfony profiler or Query Monitor for WordPress show the queries behind each page.

Understand a query with EXPLAIN

The EXPLAIN command shows how MySQL runs a query, and whether it uses an index:

EXPLAIN SELECT * FROM orders WHERE customer_email = 'name@example.com';

In the result, three columns are enough to start with: type (the value ALL means the whole table is scanned), key (the index used, empty if there is none) and rows (how many rows MySQL expects to examine). An ALL on a large table, with an empty key column, almost always points to a missing index.

The role of indexes

An index works like a book’s table of contents: instead of reading every page, the database goes straight to the right place.

CREATE INDEX idx_orders_email ON orders (customer_email);

Adding an index on the columns used in the most frequent searches, joins and sorts can cut a response time tenfold or more, without changing a line of code. For a search on several columns at once (for example status and date), a composite index on both is more efficient than two separate indexes, and column order matters: the one used for filtering first.

Traps that prevent index use

• A function applied to the column: WHERE YEAR(order_date) = 2026 cannot use the index on the date; write WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' instead.
• A search starting with a wildcard: LIKE '%smith' scans the whole table; for real text search, a full-text index or a dedicated search engine is better suited.
• Queries in a loop: a page that shows 50 orders and runs one query per order to load the customer makes 51 queries instead of 2. With an ORM, eager loading (with() in Laravel, a join with Doctrine) solves it.
• Very deep pagination: LIMIT 20 OFFSET 100000 forces the database to walk through the first 100,000 rows; for large volumes, paginate from the last ID displayed instead.

An index is not free

Each index speeds up reads but slightly slows down writes, and takes disk space. Good practice is to index the columns actually used for filtering, joining or sorting, and to drop indexes that are never used, not to index every column systematically.

Anticipate rather than suffer

A site designed from the start with indexes that match its real searches avoids most of these slowdowns. For a project already in production, a targeted audit of the most frequent queries is usually enough to bring response times back to normal. And before any structural change on a production database, take a verified backup (see the backup rule).

The checklist

1. Turn on the slow query log and note the most frequent ones.
2. Run each through EXPLAIN: spot the ALL and missing indexes.
3. Add the useful indexes, composite if the search covers several columns.
4. Fix queries that prevent index use (functions, leading wildcards).
5. Remove queries in a loop with eager loading.
6. Measure again, and drop unused indexes.

Going further: my expertise: Freelance SQL / MySQL developer · read also: Database backups: the rule almost nobody follows