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

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

• A. The app checks the cache first; on miss fetches from DB and caches result.
• B. Writes always bypass the cache and go directly to the database.
• C. The cache is updated after every read, even if not requested.
• D. The cache is populated with all data upfront.

Answer: (A)

A cache-first data access pattern can hide or bypass the work of the database indexes. B-tree indexes help the database efficiently restrict results for many queries, including certain LIKE patterns, but if the application always checks the cache first and only queries the database on a cache miss, most reads are served from memory without engaging the index-driven search. In that path, the index’s ability to optimize a LIKE filter isn’t exercised, so the practical advantage of the B-tree index for those patterns isn’t realized. When a miss occurs, you still query the database, but the frequent cache hits mean you’re not consistently benefiting from index-based filtering. That’s why the described approach is the best answer here—the caching strategy directly influences whether index-assisted query execution is actually used. The other caching scenarios don’t address this interplay between cache usage and index-enabled query optimization in the same way.

A cache-first data access pattern can hide or bypass the work of the database indexes. B-tree indexes help the database efficiently restrict results for many queries, including certain LIKE patterns, but if the application always checks the cache first and only queries the database on a cache miss, most reads are served from memory without engaging the index-driven search. In that path, the index’s ability to optimize a LIKE filter isn’t exercised, so the practical advantage of the B-tree index for those patterns isn’t realized. When a miss occurs, you still query the database, but the frequent cache hits mean you’re not consistently benefiting from index-based filtering. That’s why the described approach is the best answer here—the caching strategy directly influences whether index-assisted query execution is actually used. The other caching scenarios don’t address this interplay between cache usage and index-enabled query optimization in the same way.