MySQL: (Fulltext/Index) Search on "one-word-columns"

Question

I have a table (MySQL 5.1, InnoDB) with about 3M rows, 98% of them consist of one word only. If I am running a query using LIKE %searchterm%, it is much too slow.

As there are SELECT queries only for this table, I was thinking of converting it to MyISAM (as InnoDB does not yet, or only for 5.6+ version, support FULLTEXT).

However, I was wondering if this would really speed up the query, as from as far as I know a FULLTEXT-index is a table with split-up words ("Hello sunny day" -> "hello", "sunny", "day"), so if there are only one-words per column, would it make any sense?

Would it speed up queries if I would put a normal Index on this text-column?

Thank you in advance for your help!

Answer 1

Using a FULLTEXT index would help. It splits the text into words, but then it also indexes those words. It is this indexing that speeds up the query. But you need to use the full-text search functions, and not LIKE, to take advantage of the index.

A normal index will not help you. A LIKE clause can only take advantage of an index if it has a constant prefix.

• yourcolumn LIKE 'searchterm%' would use the index.
• yourcolumn LIKE '%searchterm%' would not use the index.