6

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!

Xsi
  • 201
  • 2
  • 11
Chris
  • 3,756
  • 7
  • 35
  • 54

2 Answers2

4

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.
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
0

Does the fulltext search or the fulltext index pose any disadvantages to you? Personally I missed searching for part of a word, such as 'Array' in 'ByteArray'.

You can consider the approach that I present here:

https://stackoverflow.com/a/22531268/543814

The idea is to store every possible suffix of a string.

This requires more storage, depending on your string lengths.

In return, you get to use a normal index for these queries: because every possible suffix of the string is in the table, LIKE %searchterm% becomes identical to LIKE searchterm%.

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

Community
  • 1
  • 1
Timo
  • 7,992
  • 4
  • 49
  • 67