Database Performance Tuning: When Queries Kill Your Scale

The application worked fine at launch. Requests completed in under 200 milliseconds, the database server sat comfortably at 15% CPU, and nobody thought much about query performance. Then user numbers grew, data volumes tripled, and suddenly the same code that ran fine in staging was producing 8-second page loads, database CPU pinned at 90%, and engineers frantically adding indexes without quite understanding why certain queries had become slow.

Database performance tuning is the discipline that prevents this trajectory — or reverses it once it has begun. The core insight is that most application performance problems are database problems in disguise. Not because databases are poorly designed, but because query patterns that are invisible at small scale become catastrophic at volume. This post covers the practical techniques for finding those patterns and fixing them before they find you.

Why Database Queries Are Different at Scale

At 1,000 rows, almost any query is fast. At 1,000,000 rows, a query without a suitable index can degrade from single-digit milliseconds to seconds. At 10,000,000 rows, that same query might never complete before the connection times out.

The reason is the query execution plan. The database engine decides how to fulfill a query by choosing from available strategies: scan every row, use an index, join via a hash or nested loop, and so on. At small data sizes, the engine often chooses full table scans because they are fast enough and simpler than index lookups. As data grows, those same scans become progressively more expensive, and the engine may not automatically switch strategies. You get a linear degradation that masquerades as a code problem until someone looks at the actual queries.

Three categories of query problems account for the majority of database-related slowdowns in PHP/Symfony applications:

1. Missing or misaligned indexes that force full scans on large tables
2. N+1 query patterns that generate hundreds of queries where one would do
3. Query structures that defeat available indexes even when indexes exist

Understanding each category — and knowing how to find it — is the foundation of any serious database performance tuning effort.

The N+1 Problem in Doctrine ORM

The N+1 query problem is the most common cause of unexpected slowdowns in applications using an ORM like Doctrine. The name describes the pattern: you execute 1 query to fetch a collection, then N additional queries — one per item — to fetch associated data. As the collection grows, so does the query count.

Here is the pattern in Doctrine:

// Fetches all orders — 1 query
$orders = $orderRepository->findAll();

foreach ($orders as $order) {
    // Triggers a new query per order to fetch the customer — N queries
    echo $order->getCustomer()->getName();
}

With 500 orders, this loop executes 501 queries. With 5,000 orders, it executes 5,001. Each query is fast in isolation — maybe 2 milliseconds — but 5,001 sequential round trips add up to 10 seconds of database time before any application logic runs.

The fix is explicit eager loading using Doctrine's query builder with a JOIN fetch:

$orders = $entityManager->createQueryBuilder()
    ->select('o', 'c')
    ->from(Order::class, 'o')
    ->join('o.customer', 'c')
    ->getQuery()
    ->getResult();

// Now getCustomer() is already loaded — no additional queries
foreach ($orders as $order) {
    echo $order->getCustomer()->getName();
}

This collapses the 5,001 queries into a single JOIN query. For paginated results with complex associations, you can also use ->addSelect() to pull in multiple relationships in one shot.

Finding N+1 patterns in a running application requires a query logger. In development, the Symfony Web Profiler shows the full query count and time per request — a request that executes more than 10–15 queries for a simple page is a strong signal. In production, you need the slow query log or a dedicated APM tool. Blackfire.io and Tideways both integrate well with Symfony and surface N+1 patterns explicitly.

Query Profiling: Finding What Is Actually Slow

Before adding indexes or rewriting queries, you need to know which queries are actually causing problems. Optimizing the wrong query — even perfectly — produces no measurable improvement.

The Slow Query Log

MySQL's slow query log is the most direct tool for finding expensive queries in production. Enable it with a threshold that catches anything meaningful but does not flood the log:

-- Enable slow query log with a 1-second threshold
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Also capture queries that do full table scans, even if fast
SET GLOBAL log_queries_not_using_indexes = 'ON';

The resulting log shows query text, execution time, rows examined, and rows sent. The ratio of rows examined to rows sent is the most useful signal: a query that examines 500,000 rows to return 10 results is almost certainly missing an index or is structured in a way that prevents index use.

Process the slow query log with pt-query-digest (from the Percona toolkit) to aggregate repeated queries and rank them by total execution time — not just the single slowest query. A query that takes 200 milliseconds but executes 10,000 times per hour is worth more attention than one that takes 5 seconds but runs twice a day.

EXPLAIN: Reading the Query Execution Plan

Once you have identified a problematic query, EXPLAIN reveals exactly how MySQL plans to execute it:

EXPLAIN SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
  AND o.created_at > '2026-01-01'
ORDER BY o.created_at DESC;

The output columns to focus on are:

• type: The join type. ALL means a full table scan — almost always wrong on large tables. ref or range means an index is being used.
• key: Which index MySQL chose. NULL means no index was used.
• rows: MySQL's estimate of how many rows it will examine. Compare this to the actual result count.
• Extra: Look for Using filesort (expensive sorting without an index) and Using temporary (results materialized into a temp table, which can be extremely slow under load).

EXPLAIN ANALYZE (available in MySQL 8.0+) runs the query and returns actual execution statistics rather than estimates — more useful when the optimizer's row count estimates are significantly wrong.

Index Design for Real Query Patterns

An index exists, the query is slow, and EXPLAIN shows the index is not being used. This is one of the most frustrating situations in database performance tuning — and it almost always has a clear explanation.

Composite Index Column Order

MySQL can only use an index from the leftmost column. An index on (status, created_at) can serve queries that filter on status alone, or on status AND created_at. It cannot serve queries that filter on created_at alone.

For a query filtering on status = 'pending' AND created_at > '2026-01-01', a composite index should put the equality condition first:

-- Correct: equality column first, range column second
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- Wrong for this query: range column first limits selectivity benefit
CREATE INDEX idx_orders_created_status ON orders (created_at, status);

Index Coverage

A covering index includes all columns referenced in the query — not just the WHERE clause, but the SELECT and ORDER BY columns as well. When a query is fully covered by an index, MySQL can return results directly from the index without touching the main table. For high-frequency queries on large tables, covering indexes can reduce query time by an order of magnitude.

-- Covering index: includes all columns the query references
CREATE INDEX idx_orders_covering ON orders (status, created_at, id, total);

Confirm coverage by looking for Using index in the Extra column of EXPLAIN output.

When Indexes Fail to Fire

Several common query patterns prevent index use even when a suitable index exists:

// Function wrapping defeats the index on created_at
->where('YEAR(o.created_at) = 2026')
// Fix: use a range condition instead
->where('o.created_at >= :start AND o.created_at < :end')
->setParameter('start', '2026-01-01')
->setParameter('end', '2027-01-01')

// Implicit type coercion defeats the index
->where('o.customer_id = :id')
->setParameter('id', "42")  // string passed for an integer column
// Fix: set the type explicitly
->setParameter('id', 42, Types::INTEGER)

Doctrine's DQL layer abstracts away some of this complexity, but generating raw SQL for critical queries and running EXPLAIN on the output is always worth the effort for high-traffic endpoints. The Symfony profiler's "Doctrine" tab shows the generated SQL for every query during a request, which makes this straightforward in development.

Query Patterns That Kill Performance at Volume

Beyond N+1 and missing indexes, a handful of query patterns cause disproportionate damage as data volumes grow.

Unbounded queries on large tables. A query without a LIMIT on a table that grows continuously will eventually take down a production database when some edge-case code path triggers it without pagination. Always add reasonable LIMIT clauses to administrative and reporting queries, and make findAll() on entity repositories a code review flag for high-traffic repositories.

COUNT with GROUP BY on unindexed columns. Aggregate queries for dashboards and reports are often written once and forgotten. At 10 million rows, a SELECT status, COUNT(*) FROM orders GROUP BY status without an index on status becomes a full table scan that runs for seconds — triggered every time a user opens the admin panel.

LIKE with leading wildcards. A query like WHERE name LIKE '%berlin%' cannot use a B-tree index at all. For substring search requirements on growing text columns, consider full-text search indexes (MATCH AGAINST in MySQL) or an external search engine.

Long-running transactions holding row locks. A web request that opens a transaction, calls an external API mid-transaction, and only commits after the API response is back can hold row locks for several seconds. Under concurrent load, other requests queue up behind those locks and latency spikes across the board. Keep transactions short and avoid network I/O inside transaction boundaries.

Monitoring Query Performance Over Time

Indexes and query rewrites fix known problems. Preventing unknown problems from accumulating silently requires ongoing monitoring.

The metrics worth tracking at the database level are:

• Slow query count per minute — a step-change increase signals a new problematic query or a data volume threshold being crossed
• Rows examined per rows sent ratio — a rising ratio indicates that index effectiveness is degrading relative to data growth
• InnoDB buffer pool hit rate — when this drops below 95%, the working set of data no longer fits in memory and every cache miss becomes a disk read
• Active connections and connection wait time — connection pool exhaustion appears here before it surfaces as application errors

For PHP/Symfony applications, a practical monitoring setup without enterprise licensing costs combines MySQL's Performance Schema, Prometheus's mysqld_exporter, and a Grafana dashboard. This gives query-level visibility and historical trending on modest infrastructure — no Datadog contract required.

Where to Start on an Existing Application

If an existing application is showing database-related slowdowns, the priority order is straightforward. Enable the slow query log and run pt-query-digest across 24 hours of production traffic — this surfaces real offenders, not the ones that feel slow in development. Run EXPLAIN on the top five queries by total execution time, not just the slowest single query. Fix N+1 patterns first, because they produce the largest gains with the least schema risk: the fix is application code, not a migration. Then add missing or misaligned composite indexes, tested against a staging environment with production-scale data volumes.

Database performance problems compound over time if left unaddressed. A code quality audit of a PHP/Symfony application's query layer frequently surfaces patterns that are invisible at current data volumes but will cause outages within 6–12 months as the dataset grows. Catching these proactively costs a fraction of what an incident response does.

If your application is showing signs of database-related slowdowns — or you want to validate that your query patterns will hold up before you hit scale — Wolf-Tech can help with a focused performance review. Reach us at hello@wolf-tech.io or visit wolf-tech.io for a free consultation.