$ ./guess_the_languageSQL & Databases
SQL & Databases · medium · #24

Why can't a B-tree index seek for this filter?

CREATE INDEX idx_name ON users (name);

SELECT * FROM users
WHERE name LIKE '%son';
  1. Strings can't be indexed at all
  2. SELECT * disables the index
  3. The leading % hides the prefix
  4. LIKE can never use any kind of index
A B-tree is ordered by prefix, so a pattern starting with a wildcard has no starting point to seek to.
Play SQL & Databases ▸