MySQL Indexes, EXPLAIN, and Performance Cheatsheet

CREATE INDEX idx_orders_created_at ON orders (created_at);

# Add an index for a frequently filtered column.

CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at);

# Index multiple columns in left-to-right order.

SHOW INDEX FROM orders;

# Display index metadata for a table.

DROP INDEX idx_orders_created_at ON orders;

# Remove an index that is unused or harmful.

EXPLAIN SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;

# View the optimizer's execution plan.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;

# Execute the query and include actual timing information.

ANALYZE TABLE orders;

# Update optimizer statistics for a table.

OPTIMIZE TABLE orders;

# Reorganize table storage and update index statistics for supported engines.

EXPLAIN FOR CONNECTION 12345;

# Obtain the plan for a statement running in another session.