Does it have a longer string in the SQL Like statement, which allows you to hinder or help request execution speed?

Question

I have a db query that will lead to a full table scan using a similar sentence and came across a question that interests me ...

Which of the following should run faster in Mysql or will they work at the same speed? Benchmarking can answer it in my case, but I would like to know why the answer. The filtered column contains several thousand characters, if important.

SELECT * FROM users WHERE data LIKE '%=12345%' 

or

 SELECT * FROM users WHERE data LIKE '%proileId=12345%' 

I can find reasons why each of them can perform the other, but I'm curious to know the logic.

Answer 1

All other things being equal, longer matching strings should work faster because they allow you to skip test strings with large steps and make fewer matches.

For an example of matching comparison algorithms, see, for example, the Wikipedia Boyer Moore algorithm .

Of course, not all things are equal, so I would definitely appreciate it.

A quick check is found in the mysql help docs in the following paragraph:

If you use ... LIKE '% string%', and the string is longer than three characters, MySQL uses the Turbo Boyer-Moore algorithm to initialize the pattern for the string, and then uses this pattern for faster searches.

Answer 2

No difference. Since you have a% sign at the beginning of your LIKE expression, this completely eliminates the use of indexes, which can only be used to match a string prefix.

Thus, it will be a full table scan anyway.

In a database of significant size (that is, which is not suitable for a bar on a 32G server), IO is the highest price with a very large margin, so I am afraid that the string matching algorithm will not matter.