Why can't B-tree indexes efficiently support certain LIKE patterns?

Why can't B-tree indexes efficiently support certain LIKE patterns?

• A. They can efficiently support patterns that match from the start of a string.
• B. They rely on sorted order enabling range scans, but cannot efficiently handle middle wildcard patterns.
• C. They can use the index for middle string patterns.
• D. They are best for full-text search.

Answer: (B)

B-tree indexes are designed to take advantage of the sorted order of keys to do fast range scans for patterns with a fixed prefix. When a LIKE pattern starts with a constant string, the database can translate that into a narrow range of index keys and scan just that portion, which is very efficient. But if the pattern has a wildcard in the middle or at the front, there isn’t a single contiguous range of keys that will match. The match depends on characters after the wildcard, so the index can’t prune the search effectively and would have to examine many entries or even the whole table. That’s why B-tree indexes can’t efficiently support certain LIKE patterns, particularly those with leading or middle wildcards. For those cases, other indexing methods or full-text/substring indexes are typically used.

B-tree indexes are designed to take advantage of the sorted order of keys to do fast range scans for patterns with a fixed prefix. When a LIKE pattern starts with a constant string, the database can translate that into a narrow range of index keys and scan just that portion, which is very efficient.

But if the pattern has a wildcard in the middle or at the front, there isn’t a single contiguous range of keys that will match. The match depends on characters after the wildcard, so the index can’t prune the search effectively and would have to examine many entries or even the whole table. That’s why B-tree indexes can’t efficiently support certain LIKE patterns, particularly those with leading or middle wildcards. For those cases, other indexing methods or full-text/substring indexes are typically used.