How to optimize a Full Text Search

Question

So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause.

Doing this search slows the query down by about 300ms and since the query is run a few hundred times a day, this amounts to quite some time lost.

The statement looks something like this:

SELECT *
FROM table1 t1
WHERE t1.code LIKE '%so%'

Where 'so' is the start of the string 'something'.

I've optimized the rest of the query, so this is really the only part that is left unoptimized.

I've tried adding an index to the column, however, since the input string doesn't always match the start of the string, it results in a search that could be anywhere within the string, meaning that a B-tree index doesn't work.

I've also tried adding a 'FULLTEXT' index, but it didn't speed up my query, it only used more disk space.

• Is there any way to speed up a full text search with variable length strings , where the searched string could be at any position within each row of the column?

The problem with the FULLTEXT index is that when using match against it has a 50% threshold, which I don't really understand. The effect it has is that it returns an empty result set, because it matches too much.

Answer 1

From Performance analysis of MySQL's FULLTEXT indexes and LIKE queries for full text search by Henning Koch:

FULLTEXT performs better when your text has low redundancy
FULLTEXT performance differs by a factor of 78 between a vocabulary of 1,000 words and 100,000 words. I guess that larger vocabularies result in a very wide but shallow inverted index that can quickly determine if a query has matches or not. An educated person has a passive vocabulary of 15,000 to 20,000 words, so FULLTEXT should work well for natural language texts.