# database

Making Indexes Work For You: Composite Indexes and Query Plans (Part 2)

Part 2 is about the practical side of indexes — the strategy behind using them well. It covers composite indexes and the leftmost prefix rule (the column order genuinely matters, and the first column can do double duty), how to use EXPLAIN to stop guessing whether your index is actually being used, the situations where indexing is the wrong call (small tables, low cardinality, write-heavy tables), and a production gotcha I learned the hard way: adding an index to a huge table isn't a casual operation and needs `algorithm: :concurrently` to avoid locking everything. Basically: "I knew indexes make queries faster — here's everything I didn't know about using them well."

Database Indexes: What Are They and Why Should You Care? (Part 1)

Part 1 starts from what you already knew — that indexes speed up queries — and then digs into the how and the surprises. It covers the full table scan problem with a million-row example, explains indexes through the book analogy, goes under the hood into how B-trees work (20 comparisons vs 1 million), and then hits the big discovery: indexes aren't free, and they can actually slow down your writes. It wraps up with a quick before/after experiment showing a 225x speed improvement. Basically: "I knew the good side, here's how it works, and here's the bad side I didn't expect."