I have a one column string(255) filled by on word - tag name, for example, radio or music. What the best way (best search result and search time) to find tags? user can input “ra” or “rad” and radio word must be present in results. Thank you.
If you are using MyISAM or MySQL 5.6+ with InnoDB then you may use the built-in full text search functionality which is pretty straight forward:
Otherwise you’ll just be using a normal index if it’s an InnoDB table on MySQL 5.5 or lower. If you search only for words that “start with” a value, such as your radio example, then those are pretty quick using a “WHERE tag LIKE “rad%”;” since that may use the standard index. If you want the functionality of “CONTAINS”, such as searching for “adi” and finding “radio”, then in that case the index will not be of any help. To optimize the “starts with” case further, you could do some analysis on your tag data and find a prefix value to use that would give you adequate selectivity while keeping the index smaller.