Overview
Performance work should begin with measurement. A slow page may be caused by an inefficient SQL query, too many round trips, large result sets, PHP processing, network latency or external services. Guessing often optimizes the wrong layer.
Find the expensive work
Capture request duration and database query timings in development or controlled production diagnostics. Identify repeated queries and N+1 patterns. Avoid logging sensitive parameter values. Use slow-query logging where available and inspect representative workloads.
Read the execution plan
Use EXPLAIN to understand how MySQL plans a query. Look for full scans on large tables, poor row estimates, unnecessary temporary tables and filesorts. Add indexes based on actual filters, joins and ordering patterns; every index also adds storage and write overhead.
Select only what you need
Avoid SELECT * for endpoints that need a few columns. Paginate large collections and ensure pagination has a deterministic order. For large exports, stream or process in batches instead of loading every row into memory.
Reduce round trips
Use joins or carefully designed batch queries where they reduce repeated calls without creating excessive result duplication. Cache expensive read-only results when invalidation can be defined safely. Use persistent connections only after understanding the hosting environment and connection limits.
Validate improvements
Compare before-and-after measurements with representative data and concurrency. Check that indexes are used and results remain correct. A query that is fast on a tiny development database may behave very differently at production scale.
Official references
MySQL EXPLAIN MySQL optimization
Measure before changing SQL
Capture the slow query, representative parameters, execution time and rows returned. Use the database's slow query log and EXPLAIN to understand the plan. On supported MariaDB/MySQL versions, EXPLAIN ANALYZE can provide actual execution information; verify availability on the server before relying on it.
Index for real access patterns
Indexes should match common filters, joins and ordering. A composite index may help a query that filters by status and then sorts by created_at, but column order matters. Avoid adding indexes blindly: each index consumes storage and increases write cost.
Keep result sets bounded
Select only required columns, paginate large collections and avoid N+1 query patterns. A page that loads 100 records and then performs one query per row can be slower than a single well-designed join or batched query.
Review PHP-side costs too
Measure serialization, template rendering, remote API calls and repeated transformations. Database tuning will not fix time spent waiting on an external service. Add timing around key operations and compare before/after results under similar load.
A repeatable optimization loop
- Record a baseline.
- Identify the dominant cost.
- Change one thing.
- Re-run the same workload.
- Check correctness and write performance.
- Keep the change only if the measured result improves.