Is this the most efficient way of searching for “RC”?
and ((title LIKE 'rc %' or title LIKE '% rc %' or title LIKE '% rc' ) or (body LIKE '% rc %') or (keywords LIKE 'rc %' or keywords LIKE '% rc %' or keywords LIKE '% rc,%' or keywords LIKE '% rc'))
Because on my website, the product named RC can appear at the beginning of a title, in the middle of a title or at the very end of a title. It can also appear anywhere in the body. It can also appear at the beginning of the keywords column, in the middle of the keywords column without the use of commas, in the middle of the keywords column followed by a comma, or at the every end of the keywords column.
Again, this query is only used for words that are three letters or less.
Consider that every “title”, “body” and “keywords” field will be evaluated
For the LIKEs that have only the trailing wildcard this could be problem enough
For the LIKEs that have the leading wildcard, even more of a problem
And because the string is only 3 characters or less, there will be less occurrence of “match fail” happening to signal a “next”
Using REGEXP might be more efficient - at least the line of code would be shorter - but I have a strong feeling that REGEXP only shifts the work to “behind the scenes” and would still take a while to run.
To improve it, move each of the different columns OR sequence into a UNION instead. It might also be faster to switch it to three queries, each for one column, and UNION the OR’s on the same keyword.
In any case, it would be inefficient and at one point when you have enough records in the table it will become too slow.
The best solution for your problem is a fulltext search engine like Elasticsearch, Sphinx etc. if not now, keep it in mind as a future update when the above query does not work anymore.