Week 23 (CST363 - Week 5)

 

Include in your journal entry for this week the following:

The web site "Use the Index Luke" has a page on "slow indexes".   https://use-the-index-luke.com/sql/anatomy/slow-indexesLinks to an external site. If indexes are supposed to speed up performance of query,  what does the author mean by a slow index? 

----

A “slow index” refers to situations where using an index doesn’t result in fast query performance—often due to the overhead of accessing many matching entries and retrieving scattered table data.

Even though indexes are designed to speed up queries, Markus Winand explains that several factors can make an index lookup unexpectedly slow:

• Multiple Matching Entries: If many rows match the search criteria, the database must follow the leaf node chain in the index to find all matches. This adds extra I/O beyond the initial tree traversal.
•  Scattered Table Access: Each matching index entry often requires a separate access to the corresponding row in the table. If these rows are scattered across many blocks, it leads to significant overhead.
• Three-Step Lookup Process:

1. Tree Traversal – Efficient and bounded by index depth.
2.  Leaf Node Chain Traversal – Can involve many blocks if multiple matches exist.
3. Table Row Fetching – Potentially hundreds of separate block accesses.