Coding Interview Preparation
📊 SQL SATURDAY #8 (BONUS) - Optimization: Why Your Query Is Slow
You've learned the syntax. Now let's talk about WHY some queries crawl on large tables - a favorite senior-level SQL interview topic.
The #1 cause: missing indexes. Without an index, the database does a full table scan - checking every single row to find matches, like reading an entire book to find one sentence.
sql
-- Without an index on email, this scans ALL rows:
SELECT * FROM users WHERE email = '[email protected]';
-- Add an index:
CREATE INDEX idx_users_email ON users(email);
-- Now the database can jump almost directly to matching rows,
-- similar in spirit to binary search on a sorted structure.
⚠️ But indexes aren't free - they speed up reads, but slow down writes (every INSERT/UPDATE also has to update the index), and they take up disk space. This is exactly why you don't index every column "just in case" - it's a genuine tradeoff, and knowing that tradeoff is what separates a junior from a senior answer here.
Second common cause: functions on indexed columns.
sql
-- This CANNOT use an index on order_date efficiently:
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- This CAN use the index:
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
Wrapping a column in a function usually forces the database to compute that function for EVERY row before it can compare - defeating the index. Rewriting the condition as a plain range comparison lets the index actually do its job.
*Third: SELECT * when you only need 2 columns* - pulling unnecessary data across the network and, if you have a covering index available, missing the chance for the database to answer entirely from the index without touching the full table row at all.
What's the slowest query you've ever had to debug and fix? 👇
1 · 393 ·