06-query-optimization.md

Query Optimization & Execution Plans

Writing a SQL query is only half the battle. In a system design or advanced DBMS interview, you are expected to understand how the database executes your query and how to make it faster.

1. The Query Execution Plan (EXPLAIN)

Interview Definition:

"The Query Optimizer is like a GPS for the database. When you ask for data, the optimizer figures out the fastest and most efficient route to fetch that data, rather than just blindly searching the whole database."

You can view this plan using the EXPLAIN keyword in most databases: EXPLAIN SELECT * FROM users WHERE age = 25;

What to look for in an EXPLAIN plan:

  • Seq Scan (Sequential/Full Table Scan): The database is reading every single row from disk. This is very bad for large tables.
  • Index Scan: The database is using an index to find the exact rows. This is good.
  • Index Only Scan: The query is entirely resolved from the index without even touching the main table data (Covering Index). This is excellent.
  • Nested Loop Join: Loops through one table and for each row, looks up matching rows in another table. Good for small datasets.
  • Hash Join: Builds a hash table of the smaller table in memory, then scans the larger table to find matches. Good for large datasets.

2. Common Causes of Slow Queries

A. Lack of Indexes

If you query WHERE email = 'test@test.com' without an index on email, the DB must scan every row.

B. Functions on Indexed Columns (Sargability)

A query is "Sargable" (Search Argument Able) if it can utilize an index.

  • Non-Sargable (SLOW): SELECT * FROM users WHERE YEAR(created_at) = 2023; Why? The database has to extract the year from every single row's created_at column before it can compare it to 2023. The index is bypassed.
  • Sargable (FAST): SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'; Why? The database can traverse the B-Tree index directly to find this range.

C. Wildcards at the start of a LIKE clause

  • Fast: WHERE name LIKE 'John%' (Can use index to jump to the 'J's and then 'Joh's).
  • Slow: WHERE name LIKE '%Smith' (Cannot use index, must scan everything because the first character could be anything).

D. SELECT * (Selecting too much data)

SELECT * forces the database to read all columns from disk, consumes more memory, and increases network payload. It also prevents the database from using Index-Only Scans. Always explicitly list columns: SELECT id, name FROM...

E. N+1 Query Problem (ORM trap)

Common in Object-Relational Mappers (like Hibernate or Django).

  • Query 1: Fetch 100 users.
  • Query 2-101: Loop through the 100 users, and for each user, run a query to fetch their posts. Result: 101 database roundtrips. Solution: Use JOINs or IN clauses to fetch everything in 1 or 2 queries.

3. Optimization Techniques

  1. Add proper indexes: Especially Composite Indexes tailored to your most common WHERE clauses.
  2. Denormalization: If JOINs are too slow, store redundant data.
  3. Pagination with Cursors: Instead of OFFSET 100000 LIMIT 10 (which makes the DB count and discard 100k rows), use a cursor: WHERE id > last_seen_id LIMIT 10.
  4. Partitioning: Splitting a massive table into smaller physical pieces (e.g., partition logs by month). Queries filtering by a specific month only scan that partition.