Fast. Simple. No meetings. No delays.
Home / Blog / PHP Development / MySQL Query Optimization for PHP Applications

MySQL Query Optimization for PHP Applications

Finding and fixing slow queries: reading EXPLAIN, adding the right indexes, avoiding N+1 patterns and measuring the result.

Cover graphic for the article: MySQL Query Optimization for PHP Applications

MySQL query optimization PHP applications need follows a loop: find the slow queries, read their EXPLAIN output, add or adjust an index, remove N+1 patterns in application code, and measure again. Most gains come from a handful of queries, so start with data rather than guesses.

Table names below are hypothetical. Behaviour differs between MySQL versions and storage settings, so test every query and EXPLAIN on a database and version that match production, with realistic data volumes.

MySQL query optimization PHP teams can repeat: find slow queries

Optimising without evidence wastes effort. Gather it first:

  • Slow query log. Enable it with a modest threshold and review the worst offenders by total time, not only by single-run time. A query that takes a short moment but runs thousands of times can cost more than a rare slow report.
  • Performance schema and sys views. Statement summary tables show which normalised queries consume the most time and rows.
  • Application-side profiling. Your framework's query log, a debug bar or APM tool shows which page triggers which queries, including duplicates.
  • Per-request counts. A page issuing several hundred queries has an application problem before it has a database problem.

Record the baseline for each target: time, rows examined, rows returned. Use a staging copy with production-like data; small development databases hide problems because everything is fast on a few hundred rows. If load is the wider concern, scaling a PHP application for higher traffic puts query work into context.

Reading EXPLAIN

Put EXPLAIN before a SELECT and MySQL shows its plan without running it. Recent MySQL 8 releases also offer EXPLAIN ANALYZE, which executes the query and reports actual timings; check your version's documentation. Columns worth understanding:

ColumnWhat to look for
typeALL means a full table scan; ref, range, eq_ref or const mean an index is helping
keyThe index actually chosen; NULL means none
possible_keysIndexes considered; useful when yours is ignored
rowsEstimated rows examined; compare with rows returned
ExtraUsing filesort or Using temporary signal extra work; Using index means covered by the index

The central question is the gap between rows examined and rows returned. Examining a huge number of rows to return a handful means the database is searching, not seeking. Also notice what blocks index use: functions applied to a column in a WHERE clause (such as wrapping a date column in a function), mismatched data types or character sets in joins, a leading wildcard in LIKE, and implicit type conversions.

Indexes that help (and hurt)

An index is a sorted structure that lets MySQL jump to matching rows. Good candidates are columns used in WHERE, JOIN, ORDER BY and GROUP BY of frequent or slow queries.

  • Composite indexes work left to right. An index on (customer_id, created_at) serves filters on customer_id, or customer_id plus a date range, but not created_at alone. Put equality columns first and the range or sort column last.
  • Covering indexes include every column a query needs so MySQL never touches the table rows, which can help read-heavy reporting.
  • Selectivity matters. An index on a column with only a few distinct values, like a status flag, rarely helps alone; combine it with other columns.
  • Foreign key columns used in joins should be indexed.

The costs: every index consumes disk and memory and slows inserts, updates and deletes, since each index must be maintained. Redundant indexes, for example one on (a) when you already have (a, b), add cost with no benefit. Review unused and duplicate indexes periodically, and add indexes via a deliberate migration rather than ad hoc, keeping in mind that building one on a large live table needs planning to avoid locking problems.

The N+1 problem

N+1 happens when code runs one query to fetch a list and then one more query per row. Loading fifty orders and then looping to load each customer yields fifty-one queries where two would do. Each individual query is fast, so the slow query log may never show it, but the total round-trip cost adds up.

Common PHP sources are ORM relationships accessed lazily inside loops, templates calling methods that query, and helper functions fetching settings repeatedly. Fixes:

  • Eager load relations in the original query. Laravel's Eloquent offers with(), and Doctrine and others have equivalent fetch joins.
  • Batch by IDs: collect the IDs, run one query with an IN list, and map results in memory.
  • Join in SQL when you need columns from both tables.
  • Cache small lookup data for the duration of a request.

Detect it by counting queries per request in development and failing tests or logging warnings when a threshold is exceeded. Some frameworks can be configured to throw on lazy loading in non-production environments.

Pagination and counting

Classic LIMIT ... OFFSET pagination gets slower as the offset grows, because MySQL still reads and discards all the skipped rows. For deep pages or large tables, use keyset (seek) pagination: remember the last seen sort key and ask for rows after it, with an index supporting that sort.

Counting has its own traps. COUNT(*) over a large table, particularly with conditions that cannot use an index, can be costly, and running it on every page view compounds the problem. Options include showing "next page" without a total, caching counts for a short time, maintaining a summary counter updated on writes, or using approximate counts where precision is not required. Also avoid SELECT * on wide tables when you need two columns, and avoid sorting large result sets on unindexed columns.

Caching results

Caching is a way to avoid work, not a substitute for fixing a bad query. Optimise first, then cache what is expensive and tolerates staleness.

  • Use an in-memory store such as Redis or Memcached for computed results, with an explicit expiry.
  • Choose keys that include everything affecting the result, such as filters and user scope, to avoid leaking one user's data to another.
  • Invalidate on write where freshness matters, or accept short expiry where it does not.
  • Guard against many requests recomputing the same expired value simultaneously, for example with a lock or staggered expiry.
  • For heavy reports, precompute into summary tables on a schedule.

Do not rely on the query cache of older MySQL versions; it was removed in current major releases, so check how your version behaves.

Measure again

After each change, re-run EXPLAIN and compare time and rows examined against the baseline. Check the whole page too, since an index that speeds one query can change plans for others. Keep a short log of what you changed and why. Re-check periodically because data grows and distributions shift; a query that was fine at a small size may degrade later. Include query counts in your regular monitoring, and make slow-query review part of routine maintenance, like keeping a PHP or Laravel application healthy. Version upgrades also affect planning; our PHP 7 to PHP 8 checklist is relevant when you modernise the runtime alongside the database.

Putting it together

Gather evidence, read the plan, add targeted indexes, remove N+1 patterns, paginate sensibly, cache what is left, and measure. If you would like an independent look at a slow application, our US software development page explains how remote engagements work, and you can reach out through the contact page.

Frequently asked questions

How do I find slow queries?

Enable the slow query log with a sensible threshold and rank statements by total time, not only single-run time. Use performance schema summaries and your framework's query log or an APM tool to see which pages trigger which queries. Test on staging data that resembles production, because small databases hide problems.

How many indexes are too many?

There is no fixed number. Each index uses disk and memory and slows writes, so keep only those that serve real queries. Remove duplicates, such as an index on (a) when (a, b) exists, and review unused ones periodically. Add an index when a measured slow query needs it.

What is the N+1 problem?

It occurs when code runs one query to fetch a list, then one extra query for each row, such as loading fifty orders and then each customer separately. The fix is to eager load relations, batch lookups by ID with one query, or join in SQL. Count queries per request to detect it.

Related articles

PHP Development USA

Scaling a PHP Application for Higher Traffic

Where PHP applications actually hit limits — database queries, sessions, caching and file storage — and the fixes in the order that pays off.

5 min read

🚀 Start Now