Query Optimization with EXPLAIN

Explain Your Queries

EXPLAIN shows how MySQL executes a statement: which tables it reads, which index it uses, and how many rows it guesses. The type column shows the access method, and ALL means a full table scan.

Sample Explain

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Check the key column. A NULL there means no index was used. Add indexes on filter and join columns to move from ALL toward range or ref.

Covering Indexes

CREATE INDEX idx_orders_cust_amt ON orders(customer_id, amount);

If every column the query needs lives inside the index, MySQL reads only the index tree. That is a covering index and it is the fastest read path.

Slow Query Log

Turn on log_queries_not_using_indexes and set a low long_query_time to capture the worst queries automatically for tuning.

Key Points

• Always EXPLAIN before optimizing.
• Move access type from ALL to range or ref.
• Covering indexes serve queries straight from the index.
• Use the slow query log to find the real offenders.