MySQL Performance Tips

EXPLAIN SELECT * FROM users WHERE email = '[email protected]'; # analyze query
EXPLAIN ANALYZE SELECT * FROM users; # execution plan with timing

CREATE INDEX idx_email ON users(email); # create index
SHOW INDEX FROM users; # show table indexes
DROP INDEX idx_email ON users; # drop index

CREATE INDEX idx_name_age ON users(last_name, first_name); # multi-column index

ANALYZE TABLE users; # update table statistics

OPTIMIZE TABLE users; # defragment and reclaim space

SHOW VARIABLES LIKE 'query_cache%'; # check cache settings
FLUSH QUERY CACHE; # clear query cache

SET GLOBAL slow_query_log = 'ON'; # enable slow query log
SET GLOBAL long_query_time = 2; # log queries > 2 seconds