Week 5

Week 5 (5/28-6/3)

The web site "Use the Index Luke" has a page on "slow indexes".   https://use-the-index-luke.com/sql/anatomy/slow-indexesLinks 

If indexes are supposed to speed up performance of query,  what does the author mean by a slow index?

Based on the site, a “slow index” isn’t a broken index; it’s simply when using an index ends up doing more work than expected, sometimes even more than scanning the entire table. After the database finds the starting point in the B-tree, it may need to traverse a chain of leaf nodes to gather all matching entries (for instance, when looking up a value “23” that appears in multiple locations). If many rows share the same key, the database follows the leaf-node chain and may read dozens or hundreds of index pages just to collect all pointers to matching rows.

Once those index entries are found, the database typically retrieves each matching row from the table one by one. If those rows are scattered across different data pages, each lookup can trigger a random I/O. For example, if your query matches 200 rows, that could mean 200 separate reads to fetch the actual data, resulting in a slower process than a simple full-table scan. In short, the B-tree lookup itself is fast, but the combined cost of following many leaf pointers plus fetching each row individually can make an “indexed” query slower than you’d expect.