MySQL: (Full Text / Index) Single Word Column Search

Question

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

As there are SELECT queries for this table only, I thought about converting it to MyISAM (since InnoDB does not exist yet or only for version 5.6+, support FULLTEXT ).

However, I was wondering if this would really speed up the query, since, as far as I know, the FULLTEXT index is a table with fragmented words ("Hello, sunny day" → "hello", "sunny", "day"), so if there is only one word in the column, will that make sense?

Would this speed up queries if I put a regular index in this text column?

Thank you in advance for your help!

Answer 1

Using a FULLTEXT index will help. It breaks the text into words, but then it also indexes these words. It is this indexing that speeds up the query. But you need to use full-text search functions , not LIKE, to use the index.

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

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

Answer 2

Is full-text search or full-text index an inconvenience for you? Personally, I skipped searching for a part of the word, for example, "Array" in "ByteArray".

You can consider the approach that I present here:

fooobar.com/questions/348157 / ...

The idea is to store all possible suffixes of a string.

This requires more memory depending on the length of your string.

Instead, you can use the normal index for these queries: since all possible line suffixes are in the table, LIKE %searchterm% becomes identical to LIKE searchterm% .

You no longer need the leading % , so the normal index can be used again.