EXPLAIN & Query Optimization

Reading EXPLAIN

EXPLAIN SELECT * FROM students WHERE department = 'CSE';
EXPLAIN ANALYZE SELECT … ;      -- MySQL 8.0.18+: actually runs it and reports real timings
EXPLAIN FORMAT=JSON SELECT … ;  -- full cost details

Key columns:

Column	What to look for
type	The access method — see the ranking below
key	Which index was chosen; NULL means none
rows	Estimated rows examined — lower is better
filtered	Percentage of rows left after the WHERE
Extra	Warnings such as Using filesort, Using temporary

type, best to worst: system → const → eq_ref → ref → range → index → ALL.

ALL is a full table scan and index is a full index scan. Either one on a large table is where you start optimising.

Extra values worth reacting to:

• Using index — good: a covering index, no row lookup needed.
• Using where — normal.
• Using filesort — the sort could not use an index; consider indexing the ORDER BY columns.
• Using temporary — an internal temp table, common with GROUP BY on an unindexed column.

The slow query log

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;              -- seconds
SET GLOBAL log_queries_not_using_indexes = 'ON';
SELECT @@slow_query_log_file;

Analyse the file with mysqldumpslow or pt-query-digest. Also useful:

SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
SHOW PROCESSLIST;         -- what is running right now

Optimisation checklist

1. Index what you filter, join and sort on. This fixes most slow queries.
2. Select only the columns you need — it enables covering indexes and cuts network traffic.
3. Keep the WHERE sargable — no functions or arithmetic wrapped around indexed columns.
4. Replace OR on different columns with UNION ALL of two indexed queries when the optimizer refuses to use an index.
5. Avoid SELECT COUNT(*) on huge tables for pagination; use an approximate count or keyset pagination.
6. Batch writes. One 1000-row INSERT beats 1000 single inserts.
7. Use EXISTS instead of IN for large, correlated subqueries.
8. Delete in chunks — DELETE … LIMIT 10000 in a loop instead of one giant statement that holds locks for minutes.

Rewriting examples

-- before: cannot use the index on created_at
SELECT * FROM logs WHERE DATE(created_at) = '2026-09-17';
-- after
SELECT * FROM logs
WHERE created_at >= '2026-09-17' AND created_at < '2026-09-18';

-- before: deep offset reads and discards 100k rows
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 100000;
-- after: keyset pagination
SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 20;

Server-level tuning

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

The single most important setting is innodb_buffer_pool_size — on a dedicated database server, set it to roughly 60–75% of RAM so the working set stays in memory. After that, look at max_connections, tmp_table_size and connection pooling in the application.

Optimise in this order: schema and data types, then indexes, then the query, then server configuration, then hardware. Measure before and after each change — assumptions about what is slow are wrong more often than not.