CST363 - Week 5

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

Article: "Use the Index Luke" - https://use-the-index-luke.com/sql/anatomy/slow-indexes

 
Based on what I learned from the article above, even though indexes are designed to speed up queries, the author explains that certain situations can make index lookups slower than expected. One reason is that after finding a matching value in the index, the database may need to follow the leaf node chain to check for additional matches. This extra step adds to the lookup time. Another reason is that even after finding matches in the index, the database must still retrieve the actual table data, which is often spread across multiple blocks. This extra work can slow down queries, especially if the index range scan has to read a large portion of the index.

The author also discusses common myths about slow indexes. Some people believe that an index becomes broken or unbalanced, making queries slow, but the real issue is usually how the index is used rather than its structure. The article describes different types of index scans, such as INDEX UNIQUE SCAN, which is fast because it only searches for one result, and INDEX RANGE SCAN, which can be slower because it needs to find multiple matching entries. If a query retrieves many rows and needs to access the table for each one, it can become slow despite using an index. This shows that while indexes are useful, they do not always guarantee fast performance.