What is the query execution plan and how does the MySQL optimizer work?

The query optimizer is MySQL's component that determines the most efficient way to execute a query. It analyzes the SQL, considers available indexes, table statistics, and join orders, then produces an execution plan — the sequence of operations to retrieve the data. MySQL uses a cost-based optimizer: it estimates the cost (I/O and CPU) of various execution strategies and chooses the one with the lowest estimated cost. The optimizer considers: which indexes to use (or whether to scan the table), join order (for queries joining multiple tables — it evaluates permutations, but caps at 61 tables for performance), index selectivity (high-cardinality indexes are preferred), table row counts (from statistics). Table statistics are crucial for the optimizer — it uses ANALYZE TABLE to update statistics. Stale statistics cause suboptimal plans. Viewing the plan: EXPLAIN (estimated), EXPLAIN ANALYZE (actual execution, MySQL 8.0+). Optimizer hints (MySQL 8.0+): SELECT /*+ INDEX(users idx_email) */ * — force a specific index; /*+ JOIN_ORDER(users, orders) */ — force join order. Optimizer switches: SET optimizer_switch = "index_merge=off";. Statistics for optimizer: SHOW INDEX FROM tablename\G — shows cardinality estimates.