A slow index is where a combination of “unoptimized” lookups are made.
For example:

• Following the leaf node chain (such as looking ahead to see if the number exists)
• Accessing the different table blocks because the leaf node chain provided multiple results

This down to the fundamentals of how a lookup is performed. There are three steps:

1. the tree traversal (fast)
2. following the leaf node chain (can be slow)
3. fetching the table data (can be slow)

The solution to this is to see how a database will perform your query, or in other words, the solution is to perform query optimization.