What is query optimization in MySQL?

Query optimization is the process of making SQL queries execute faster. Key techniques: (1) Use indexes: add indexes on columns in WHERE, JOIN ON, ORDER BY, GROUP BY — verify with EXPLAIN that the index is used; (2) Avoid SELECT *: specify only needed columns — reduces data transfer and can enable covering indexes; (3) Avoid functions on indexed columns in WHERE: WHERE YEAR(created_at) = 2024 can't use index on created_at — rewrite as WHERE created_at BETWEEN "2024-01-01" AND "2024-12-31"; (4) Use covering indexes: an index that contains all columns needed by the query — MySQL can satisfy the query from the index without touching the table data (EXPLAIN shows "Using index"); (5) Limit result sets: use LIMIT, filter early; (6) Optimize JOINs: ensure JOIN columns are indexed; (7) Avoid correlated subqueries: rewrite as JOINs or use EXISTS; (8) Use pagination wisely: keyset pagination instead of OFFSET for large pages; (9) Analyze query plans: use EXPLAIN ANALYZE in MySQL 8.0+; (10) Profile queries: SET profiling = 1; then SHOW PROFILE;; (11) Rewrite OR as UNION: OR conditions can prevent index use.