So we have this
VARCHAR(255) column called
code, which is a string of varying size we use in a
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.
SHOW CREATE TABLEand some examples of the 255 characters in "code", plus more examples of search strings (in addition to '%so%'; we might be able to come up with a workaround involving either the schema or the technique for searching.