Indexes that actually help are the ones a slow query asks for when you run EXPLAIN. A MySQL index is a sorted copy of one or more columns that makes lookups cheap. Start from the query itself in phpMyAdmin, not from a plugin that promises a one-click database speedup. Prefix the statement with EXPLAIN and read what it returns. If type is ALL on a large table, you lack an index that matches the WHERE or JOIN. Add that index. Ten extra “just in case” indexes slow every INSERT and UPDATE.
How to read an EXPLAIN result
The type column is the headline. Values like ref, eq_ref, and range mean MySQL is using an index. ALL means a full table scan. The rows value is the optimizer’s guess at how many rows it will touch. When Extra shows Using filesort or Using temporary on a busy query, fix that next, often with a composite index that covers both the WHERE and the ORDER BY. If key is NULL, MySQL looked at your indexes and chose none of them.
On WordPress sites, wp_postmeta is the usual gap. Plugins often look up meta_key, or post_id plus meta_key, on a table whose default indexes stop fitting after years of product and order meta. One composite index on (meta_key, post_id)—or the pair your EXPLAIN actually shows—helps more than a blanket “optimize tables” job.
What a useful MySQL index is not
A covering index can answer a query from the index alone when the columns are short, and that is a real win. A 500-byte VARCHAR as a unique key taxes every write and widens pages in the buffer pool. Prefix indexes exist as a compromise, not as a default choice. Unique keys exist for uniqueness, not as a substitute for a matching lookup path.
A plugin that “optimizes tables” every night without adding indexes is doing a different job, and often a harmful one on InnoDB. Rebuilds are not lookups. If writes got slower after you added indexes on every column a report might filter, that tax is showing up. Drop the indexes EXPLAIN never chooses.
Find the next slow query
Once this query is cheap, the slow query log is how you find the next one. See the slow query log for how to turn it on and read what it records.
Tagged
Was this article helpful?
Be the first to rate this article.
