I need to implement an "autocomplete-like" query in MySQL, one on a fairly short textual field varchar(11) and one on a potentially long field varchar(140).

As they are autocomplete query, they will be LIKE-query. What sort of indexes I need to configure?


not really big choice - it will be normal B-Tree index

with autocomplete You can not use FullText because it work only with full words

at the same time queries like:

SELECT text FROM tag_table WHERE text LIKE 'part_text%';

will use indexes


in case of big column -

You of course could try to realise auto-complete by whole words - there You can create full text index, but not sure - how good it will work, because with fulltext always a lot of variants at the begin of search phrase, and variants how suggested text will be relevant to expected result

  • because it work only with full words. but no. you can do word* to match non complete words
    – Mahdyfo
    Apr 16 '18 at 16:18

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.