Why would a mysql SELECT with 'LIKE word%word2' in the where clause not use an index

The query is large and includes a long list of 'LIKE' tests in the WHERE clause, e.g., ...SELECT colA FROM t WHERE (colX LIKE 'word1%word2%' OR colX LIKE 'word3%word4%' OR...);

colX has an index. mysql uses the index since the comparison doesn't start with a '%'. By checking with EXPLAIN I see that when the SQL string gets bigger mysql stops using the index and starts doing full table scans. It seems to be related to the number of 'LIKE' tests in the where clause. At the threshold I can add one more 'LIKE' and it stops using the index and takes 10 times longer th开发者_C百科an without the extra 'LIKE'.

Is there some mysql variable that controls behavior like this?

I believe that if you add FORCE INDEX [index_name], it will use index in any case. Also, check Hint index documentation. The value of sysvar_max_seeks_for_key also affects whether the index is used or not. Maybe changing this value to a smaller number will help.