Week 5 - Slow Indexes Reflection

 Referencing Slow Indexes in RDBMS: if indexes are supposed to speed up performance of query, what does the author mean by a slow index? 

    In my time working with indexes thus far, I have seen what miracles they can work for improving query performance. This is my first encounter with unpacking how indexes could actually slow a query down. Author Markus Winand illustrates how index lookups can still be slow and expensive even when functioning properly. Winand explains how index performance entails more than just tree traversal in a clear and inviting fashion. First, the lookup conducts the tree traversal by navigating down the index B-tree to reach the intended leaf node. Upon following the leaf node chain, multiple hits can mean the database needs to retrieve all matching index entries. For each matching index entry, the database will then access the corresponding row in the table, which could be located entirely elsewhere in memory or disk blocks. Winand details how tree traversal is fast but the other two steps are expensive. If the result set its large, the system will read many leaf nodes and perform many table lookups.

    Winand then goes on to explain a common myth about "broken indexes" needing to be rebuilt in order to improve performance when in actuality the issue lies with the nature of the query and how much data needs to be accessed. Mentioning Oracle, Winand broke down this process into the following distinct operations:

I. INDEX UNIQUE SCAN: Fast — used when only one match is expected.

II. INDEX RANGE SCAN: Slower — follows the leaf node chain for multiple matches.

III. TABLE ACCESS BY INDEX ROWID: Fetches the actual row from the table — often most expensive.

Thus, the author implies a query that is accessing many rows and table blocks can lead to the appearance of a slow index but that the true issue lies in the importance of solid query design.