Why mysql SELECT with "LIKE word% word2" in where clause doesn't use index

Question

The request is large and includes a long list of LIKE tests in the WHERE clause, for example ... SELECT cola FROM t WHERE (colX LIKE 'word1% word2%' OR colX LIKE 'word3% word4%' OR ...);

colX has an index. mysql uses an index because the comparison does not start with "%". Checking with EXPLAIN, I see that when the SQL row gets larger, mysql stops using the index and starts to perform a full table scan. This seems to be related to the number of "LIKE" tests in the where clause. At the threshold I can add another "LIKE", and it stops using the index and takes 10 times more than without the additional "LIKE".

Is there some kind of mysql variable that controls this way?

Answer 1

I believe that if you add it FORCE INDEX [index_name], it will use the index anyway. Also, check the help index documentation . The value of sysvar_max_seeks_for_key also affects the use of the index or not. Perhaps changing this value to a smaller number will help.