Optimizing Slow MySQL Queries: EXPLAIN and Indexes, by the Numbers

Optimizing slow MySQL queries goes through three ordered steps: first catch the heavy queries by enabling slow_query_log, second understand why each query is slow with the EXPLAIN command, and third fix by adding the right index or rewriting the query. To enable the slow query log you set slow_query_log = ON and long_query_time = 1, so MySQL logs every query exceeding one second along with the number of rows examined and the execution time. After gathering a sample, you take the slowest query and put the word EXPLAIN in front of it, which shows the execution plan: does MySQL scan the whole table (type=ALL, the worst) or jump straight through an index (type=ref or eq_ref, the best), how many rows does it expect to examine (the rows column), and does it fall back to an expensive temporary sort on disk (Using filesort or Using temporary). The right index turns a query from scanning a million rows to reading just ten, dropping response time from seconds to milliseconds and cutting vCPU use by a margin you can actually measure in the server dashboard. This article explains each step practically: how to read EXPLAIN field by field, when a composite index helps and how to order its columns, and the trap that voids your index without you knowing.

Step One: Catch the Slow Query

Do not optimize by guessing. Enable slow_query_log and let the application run under real load for a few hours, then read the resulting file with the mysqldumpslow tool, which groups similar queries and ranks them by total impact, revealing the real list of culprits. You often discover that one query repeating thousands of times consumes more than a giant query running once a day. Focus on total impact (execution count times per-run time) rather than the single slowest query, because optimizing a query that runs 5000 times an hour by saving 200 milliseconds each far outweighs optimizing a slow nightly report that runs once.

Step Two: Read EXPLAIN

Put EXPLAIN before a SELECT query and you get the execution plan in one or more rows. The four most important columns follow. The type column tells you the access method ranked worst to best, with its key values in the table below. The rows column estimates how many rows MySQL will examine, and lower is better, so a number in the millions is a direct alarm. The key column tells you which index was actually used, and if it is NULL no index is used and the scan is full. The Extra column carries precious warnings: Using filesort means an extra costly sort because your ordering does not rest on an index, and Using temporary means a temporary table in memory or on disk, both of which drain resources and should be removed if possible. Using index, by contrast, is good news meaning the query was answered from the index alone without ever touching table data.

Step Three: Add the Right Index

An index is a side data structure that pre-sorts a column's values so MySQL jumps to the wanted row instead of scanning everything. The rule: index the columns that appear in WHERE conditions, in JOINs, and in ORDER BY. But not every index helps, and over-indexing slows writes (INSERT/UPDATE) because every index must be updated with each change, and it consumes extra disk space. The answer is balance: index what you actually search on and often, not every column just in case. After adding an index rerun EXPLAIN and confirm that type improved and rows dropped substantially, because verification by numbers, not by feel, is the standard.

The Composite Index and Its Column Order

When your query searches on two columns together (say user_id and status), a composite index on both is stronger than two separate indexes. But the order is decisive and governed by the "leftmost prefix rule": a composite index on (user_id, status) serves a search on user_id alone, and a search on user_id and status together, but it does not serve a search on status alone. The practical rule: put first the column that appears in the most selective equality condition (the one that narrows rows the most), then range and ordering columns. A wrong order means an index that exists but goes unused, the worst case because it costs space and maintenance for nothing.

The Trap That Voids Your Index

The most famous mistake that silently kills an index: applying a function to the indexed column inside a WHERE condition. If you write a condition wrapping the column in a function, such as YEAR on a date column or LOWER on a text column, MySQL cannot use the index and reverts to a full scan, because the index is built on the raw value, not the function's output. The fix is to rewrite the condition so the column stays bare on the left of the comparison: instead of wrapping the date column in a function that extracts the year, compare the column directly against a date range (from the start of the year to its end). The golden rule: keep the indexed column bare on the left side of the condition, and move any computation to the other side of the comparison. Another similar trap: comparing across different types (a number against a string) forces MySQL into an implicit conversion that voids the index too, so always match the value type to the column type.

The Effect of Optimizing One Query on vCPU

Optimization is not a theoretical luxury, it reflects as a number on your resource bill. A query scanning a million rows drives the processor and disk hard, and if it repeats thousands of times an hour under real load it can push vCPU use to its peak and slow every other request on the same server. Turning that query into a ten-row read via a single index can cut database load by a large margin, so the server breathes and serves more visitors on the same specs. This means, in practice, that optimizing a query may defer the need to upgrade the plan. And if you have genuinely hit the ceiling after optimizing, upgrading your cloud server is instant: you add a vCPU for 500 SAR per year or 2GB RAM for 600 SAR per year with no downtime. Review the plans and pricing to balance code optimization against hardware upgrades.

The Practical Takeaway

Do not optimize by guessing: enable slow_query_log and catch the real culprits by total impact. Read EXPLAIN and focus on type, rows, key, and Extra, and beware of Using filesort and Using temporary. Add an index for WHERE, JOIN, and ORDER BY columns without overdoing it, and order composite index columns by the leftmost prefix rule. Never wrap an indexed column in a function, and match value types. Always verify by rerunning EXPLAIN after each change, because the number is the judge, and one optimized query may save you a whole upgrade.