Table indexes for autocomplete in MySQL 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 

add:

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