Re: How to Troubleshoot Slow MySQL Queries Using EXPLAIN and Indexes
Hello,
Solid list. A few more that come up a lot in practice:
Slow Query Log is worth turning on before reaching for EXPLAIN on individual queries, since it catches the ones you didn't know were slow:
Commands:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
Then review with mysqldumpslow or pipe it into pt-query-digest (Percona toolkit) for a ranked summary of your worst offenders by total time, not just single execution time.
EXPLAIN ANALYZE (MySQL 8.0.18+) is worth using over plain EXPLAIN when available, plain EXPLAIN just shows the planned execution, EXPLAIN ANALYZE actually runs the query and shows real timing per step, which catches cases where the optimizer's estimate was way off from reality.
Watch for implicit type conversions in WHERE clauses, comparing a VARCHAR column against an integer literal (or vice versa) silently disables index usage even if the index exists, EXPLAIN will show a full scan and it's not obvious why unless you check column types match the literal type being compared.
Composite index column order matters a lot. An index on (customer_id, order_date) helps a query filtering on both, or just customer_id, but won't help a query filtering on order_date alone, leftmost prefix rule. Worth mentioning alongside the "don't over-index" point since order is often the actual mistake, not the index count.
Covering indexes are underused, if a query only needs a few columns and they're all in the index itself, MySQL can satisfy it straight from the index without touching the table row at all (visible as "Using index" in EXPLAIN's Extra column), noticeably faster on large tables.
Good conclusion overall, EXPLAIN + indexing + avoiding SELECT * covers most real world cases, the slow query log is really the missing first step for finding what to even investigate.
Regards,
Herrick Peterson
DevOps Engineer @accuweb.cloud