Re: CPU usage 99% - and query very slow - help

[Øystein Grøvlen]

I see your slow log contains a lot of queries with predicates like "search_name LIKE '%Kaspersky%'". Note that the LIKE operator can only use indexes when searching for prefixes of strings. Hence, when searching for strings starting with '%', normal indexes will not be used.

I see you have full text indexes defined. However, in order to use them, you need to use the MATCH function. That is, instead of the LIKE-expression above, you should write something like "MATCH(search_name) AGAINST ('Kaspersky')".

Fult text indexing for InnoDB will be available in the upcoming MySQL 5.6 release.

HTH,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway