Taming Slow Queries: Read the Plan Before Optimising

When a system slows down, the usual reflexes are adding indexes and then caching. Both sometimes work and sometimes push the problem three months out. Our habit is to get the execution plan first, then decide what to change.

Our order of investigation

Enable the slow query log and sort by total time, not by single execution. A 200 ms query running fifty times per second matters more than a five-second query that runs occasionally.

Run EXPLAIN and read access type, rows scanned and index usage. A type of ALL with large rows usually confirms the problem.

Check for implicit type conversion. Comparing a string column against a number silently disables the index.

Common mistakes

Indexing every column, which slows writes and bloats storage for little gain.

Tuning SQL without looking at the business need; often the query fetches far more data than required.

Treating cache as a fix. When the hit rate drops, the database takes a larger instantaneous load.

Slow query work never really ends. Every project keeps a query list and reviews its ten slowest statements monthly after launch.