When indexes help vs hurt: the write cost nobody mentions
An index isn't free. It speeds up the reads that use it and slows down every single write to that table. Knowing which columns are worth it is the actual skill.
An index is a separate, sorted structure the database keeps next to a table, so it can jump straight to matching rows instead of scanning the whole table. That speed isn't free: every index also has to be updated on every write.
🎯 Explain Like I'm Hired An index is like the index at the back of a book: flip straight to the page you need instead of reading the whole book. But every time a new page gets added to the book, the index page also has to be updated, which takes extra work. Example: if a table gets thousands of new rows a minute, an index that no query actually uses is pure cost. It slows down every one of those inserts without ever speeding up a read.
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
Sign up to keep reading
Sign up free to unlock the worked examples, edge cases, and interview traps below.