SQL Query Cost Estimator

EXPLAIN shows the query execution plan — how the database engine plans to execute your query. It reveals whether indexes are used (Index Scan vs Seq Scan), join strategies (hash join, nested loop, merge join), estimated row counts, and cost estimates. EXPLAIN ANALYZE actually runs the query and shows real execution times. Reading EXPLAIN output is the most important skill for query optimization.

A sequential scan (Seq Scan) reads every row in a table regardless of the WHERE clause. It's fine for small tables or queries that return most rows. It's a problem when you have millions of rows and only need a few — that's when an index scan would be 100-1000x faster. The query planner chooses Seq Scan when: no suitable index exists, the table is small, or the query returns a large percentage of rows (typically >10-20%).

Indexes trade write performance for read performance. A B-tree index allows O(log n) lookups instead of O(n) sequential scans. Composite indexes (a, b, c) support queries filtering on the leading columns. Partial indexes add a WHERE clause and are smaller. Covering indexes include all queried columns (index-only scans avoid table reads entirely). Over-indexing hurts INSERT/UPDATE/DELETE performance and increases storage.

Nested Loop Join: O(n*m) — fast for small tables or when inner side is indexed. Hash Join: O(n+m) — builds hash table on smaller side, good for large unsorted tables. Merge Join: O(n log n + m log m) — requires both sides sorted, good for ordered data or sort-merge operations. The query planner picks the best strategy based on statistics. Wrong choices happen when table statistics are stale — run ANALYZE regularly.

Rows examined: how many rows the engine reads from storage (or index). Rows returned: how many rows match the query filters. A ratio of examined/returned > 100 is a red flag — you're scanning many rows to find few. This ratio is a key metric in MySQL's slow query log and EXPLAIN output. A good index reduces this ratio toward 1:1. High ratios indicate missing indexes, poor selectivity, or queries that need redesigning.