A LIKE with a leading wildcard cannot use the index

An index on a string column is a B-tree ordered by the leading characters, so MySQL can find a range only when it knows where the range starts. A pattern that begins with a literal prefix gives it that; a pattern that begins with % does not, and the optimiser falls back to reading every row.

EXPLAIN SELECT id, sku FROM products WHERE sku LIKE 'FR-100%';
-- type: range   key: idx_sku   rows: 12

EXPLAIN SELECT id, sku FROM products WHERE sku LIKE '%100%';
-- type: ALL     key: NULL      rows: 51834

The trap is that both queries look equally selective and the second one is often faster in testing, because 50,000 rows fit in the buffer pool on a development machine. It stops being fast at the size where it matters. If the search genuinely has to match the middle of a value, the options are a FULLTEXT index — MyISAM only, InnoDB has no equivalent — or a second column holding the value reversed, which turns a trailing wildcard back into a leading literal.