mysqlpattern-matchingsql-likequery-performancesqlperformance

MYSQL. LIKE-query becomes too long if I use a variable

I have the big table with 5 million words. I need to find words beginning with dynamic string. But when I use LIKE-operator with a string-variable, query becomes too long.

For example, this query takes 1.3 seconds:

set @pattern = 'f%';
select * from words where Word like @pattern limit 100;

Next query takes 0.0 seconds:

select * from words where Word like 'f%' limit 100;

But both queries perform the same work.

What could be the reason why requests are very different in duration? How to eliminate this difference?

Solution

  • It turned out that variables have utf8mb4 charset (default charset of my database), but column Word has utf8 charset.

    So, all I need to do is convert the charset using utf8:

    set @pattern = CONVERT('f%' USING utf8);
select * from words where Word like @pattern limit 100;