CST 363 - Week 5

 Slow Index:  https://use-the-index-luke.com/sql/anatomy/slow-indexes

Links to an external site.

 Everything has two sides, and index is not an exception. There are situations where index won't be useful as improving performance for query. Those situations create a result called "slow index".

The first situation mentions by the author is when there are multiple leaf nodes match with given index. Because of that, even when we can reach our first matching node really fast, we have to keep travelling through the leaf node chain and check for all matching nodes. It will definitely consume more time, effecting the performance a lot more than usual index searching.

The second situation is different. Index searching is still fast, but the data reading performance won't improve. That is because the node contains the index may have multiple rows, and those rows may come from multiple data tables instead of one. Moreover, multiple data tables may come from multiple files, which require couple I/O operations. This problem falls into other categories rather than the index searching algorithms, but it still makes the index become "slow index".