1

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.

  • Please provide the entire SHOW CREATE TABLE and 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. – Rick James May 3 '17 at 14:41
  • @RickJames Unfortunately I do not have permission for showing the create table output, as well as giving the structure of "code". All I can say is that code is a generated string based on multiple results of our application layer. We don't store these results in another table or column – FMashiro May 4 '17 at 8:25
0

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.

  • It doesn't necessarily have a low redundancy, however the string we search are so small and generic that it doesn't return anything because of the threshold used in FT index searches – FMashiro May 3 '17 at 7:29
  • Can you not tune the FT index threshold to give back fewer results? – Oreo Feb 15 '18 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.