Optimisation for MySQL DBAs
MySQL optimisation is the practice of making queries and the server use fewer resources and return faster. It relies on EXPLAIN to read execution plans, well chosen indexes, rewritten queries and the slow query log, and works from measurement rather than guesswork.
Stage: Performance · Part of the MySQL DBA roadmap · Last reviewed 19 November 2026
What to learn in Optimisation
- Read EXPLAIN output and spot a full table scan
- Create a composite index and order its columns for the query
- Enable the slow query log and rank statements with pg style digests from performance_schema
- Rewrite a query to avoid functions on indexed columns
- Use EXPLAIN ANALYZE to compare estimated and actual row counts
Optimisation interview questions
Why might MySQL ignore an index you created?
Common causes are a function or implicit type conversion on the indexed column, a leading wildcard in LIKE, low selectivity so a scan is cheaper, or a composite index whose leftmost column is not used in the filter. EXPLAIN shows what the optimiser actually chose.
What does the leftmost prefix rule mean for composite indexes?
A composite index on (a, b, c) can serve filters on a, on a and b, or on a, b and c, but not on b or c alone. Column order should match the most common equality filters first, then range columns.
Where Optimisation fits in the MySQL DBA roadmap
Optimisation belongs to the Performance stage (intermediate level). Make it fast.
Other units in this stage
Back to the full MySQL DBA roadmap