How to Profile and Optimize MySQL Queries for High-Traffic PHP Applications

A PHP application that feels fast in development can slow to a crawl in production the moment real traffic and real data volume arrive. In almost every case we've profiled, the bottleneck isn't PHP itself, it's a handful of MySQL queries doing far more work than they need to. This guide walks through finding those queries, understanding why they're slow, and fixing them with the right indexes.

Step 1: Find the Slow Queries First

Guessing which query is slow wastes time. MySQL's slow query log tells you exactly which queries are expensive, how often they run, and how long they take. Enable it in my.cnf (or a conf.d include file) before you touch any code:

# my.cnf / mysqld.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

Setting long_query_time = 0.5 logs anything taking longer than half a second. On a high-traffic application, half a second per query is already too slow, most queries backing a user-facing page should return in single-digit milliseconds. Start strict, then loosen the threshold later if the log becomes too noisy to act on.

Step 2: Read the Execution Plan with EXPLAIN

Once you have a slow query, run EXPLAIN in front of it to see how MySQL's optimizer actually intends to execute it. Four columns matter most:

A query on a 2-million-row orders table with type: ALL and rows: 1,987,432 is scanning nearly the entire table for every execution. Under light traffic that might go unnoticed; under concurrent load it's what pins your CPU and queues up connections.

Diagram: Full Table Scan vs. Indexed Lookup

Step 3: Build the Right Index with the ESR Rule

Adding an index isn't enough on its own, column order inside a composite index determines whether MySQL can actually use it efficiently. The ESR rule gives a reliable order to follow: Equality, Sort, Range.

• Equality — columns used with = in your WHERE clause go first.
• Sort — columns used in ORDER BY go next, so MySQL can read results already sorted instead of running a filesort.
• Range — columns used with >, <, BETWEEN, or LIKE 'x%' go last, since a range condition stops the index from being useful for any column that follows it.

Diagram: ESR Composite Index Structure

Applied to a typical orders table query, look up a customer's orders, newest first, limited to a status range:

-- The query we're optimizing for
SELECT id, total, status, created_at
FROM orders
WHERE customer_id = 4821
  AND status > 'pending'
ORDER BY created_at DESC;

-- The ESR-ordered composite index
ALTER TABLE orders
ADD INDEX idx_customer_created_status (customer_id, created_at, status);

Note that status, the range condition, sits last, even though it appears in the middle of the WHERE clause. Putting a range column before a sort column breaks the index's ability to serve the ORDER BY for free, forcing MySQL back into a filesort. Run EXPLAIN again after adding the index, you should see type shift to ref and Extra drop any mention of Using filesort.

Step 4: Confirm the Fix Under Real Load

An improved EXPLAIN plan is a good sign, but the real test is response time under concurrent traffic. Re-run the query through your application's normal path, check the slow query log again after a traffic window, and confirm the query has dropped out of it entirely. Query tuning and application-layer tuning compound each other, a well-indexed query still has to be served by PHP efficiently once the data comes back.

Closing Thoughts

Most "slow application" complaints trace back to a small number of unindexed or badly-ordered queries, not PHP itself. The workflow is always the same: log what's actually slow, read the execution plan honestly, and build indexes that match how the query filters, sorts, and ranges, in that order. Fix the worst offenders first; a handful of corrected indexes usually accounts for the majority of the win.