Query Optimization

How the Database Engine Thinks

When you write a query, the database doesn't just execute it blindly. The query optimizer analyzes the query and finds the most efficient way to execute it. Understanding this process helps you write faster queries.

Query Execution Steps

  1. Parse      → Check SQL syntax, build parse tree
  2. Validate   → Check table/column names, permissions
  3. Optimize   → Choose best execution plan (uses indexes? joins order?)
  4. Execute    → Run the chosen plan
  5. Return     → Send results to client

EXPLAIN: See the Plan

  EXPLAIN SELECT * FROM Students WHERE Name = 'Alice';

  ┌─────┬─────────────┬───────┬──────────────────────┐
  │ id  │ select_type │ table │ type                 │
  ├─────┼─────────────┼───────┼──────────────────────┤
  │ 1   │ SIMPLE      │Students│ ALL (full scan)      │
  └─────┴─────────────┴───────┴──────────────────────┘

  After adding index:
  ┌─────┬─────────────┬───────┬──────────────────────┐
  │ id  │ select_type │ table │ type                 │
  ├─────┼─────────────┼───────┼──────────────────────┤
  │ 1   │ SIMPLE      │Students│ ref (index lookup)   │
  └─────┴─────────────┴───────┴──────────────────────┘

Common Optimization Tips

• Use indexes on columns in WHERE, JOIN, and ORDER BY.
• Avoid SELECT * — only select the columns you need.
• Use JOIN instead of subqueries when possible.
• Filter early — use WHERE before GROUP BY.
• Avoid functions on indexed columns — WHERE YEAR(Date) = 2024 can't use an index on Date.
• Use appropriate data types — INT is faster than VARCHAR for numeric IDs.