Query Optimization

Code

-- Inspect the execution plan
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at;

-- Avoid SELECT *; project only needed columns
SELECT id, total FROM orders WHERE customer_id = 7;

-- Composite index covering WHERE and ORDER BY
CREATE INDEX idx_cust_created ON orders(customer_id, created_at);

-- Covering index includes selected columns
CREATE INDEX idx_cust_total ON orders(customer_id, total);

-- Find slow queries (enable first)
SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Analyze table for stats refresh
ANALYZE TABLE orders;

Explanation

EXPLAIN reveals the access method (const, ref, range, scan) and indexes MySQL plans to use. Composite indexes that cover WHERE, JOIN, and ORDER BY columns let MySQL serve a query from the index alone, avoiding table lookups. Enable the slow query log to identify queries worth optimizing.

More MySQL Snippets