Week 20

This week I read about “slow indexes” on Use the Index, Luke, and it helped clear up a common misconception. I always thought that if a query uses an index, it should automatically run faster. But the author explains that even indexed queries can be slow depending on how the database has to look up the data.

A slow index happens not because the index is broken, but because of how much work the database still has to do. First, it finds the right spot in the index tree, but then it may need to follow a long chain of leaf nodes if there are multiple matches. After that, it has to go back to the main table to fetch the actual rows, which might be scattered in different places. All those extra reads can make the query take longer.

The site also describes three kinds of scans: an INDEX UNIQUE SCAN, which is the fastest because it finds only one match; an INDEX RANGE SCAN, which has to go through several entries; and TABLE ACCESS BY INDEX ROWID, which retrieves the full data from the table. When a query has to do many range scans and table lookups, it can become slow even though it’s technically using an index.

What stood out to me is that a “slow index” isn’t really a problem with the index itself, it’s about how the query interacts with the data. It’s a good reminder that optimizing performance isn’t just about adding indexes, but also about writing smarter queries and understanding how the database reads information.