CODEWITHSUNDEEP
mysqlperformancequery-optimizationinnodb
Terminal
Terminal

Reputation: 1999

Does indexing a text in mysql improve searching text with LIKE '%search%'

I am using InnoDB engine in mysql version:5.1.61-community-log. The length of text to be searched is less than 100 characters. Does the performance of queries with LIKE '%searchstring%' improve with indexing the text ?

EDIT:
I am using this query for jquery auto-suggest component.
I am using 3rd party webhosting service . So upgrade is not an option for me

Upvotes: 3

Views: 1856

Answers (1)

Mark Byers
Mark Byers

Reputation: 837926

No, adding an index won't help your LIKE '%foo%' queries (much*). A LIKE condition can only use the index efficiently if you have a constant prefix, such as LIKE 'foo%'

You should consider a full text search instead. If you are on MySQL 5.5 or older only the MyISAM engine was supported. In MySQL 5.6 or newer InnoDB is also supported for full text searches.

If you are using an older version of MySQL, are unable to upgrade, and are unable to change the storage engine (because you need other features of InnoDB such as foreign key constraints), then you could consider creating a new MyISAM table which stores only the primary key and the text column. You can use this table to perform fast full text searches and join to the original table if you also need access to the other columns.

You could also consider using an external text search engine such as:

(*) If the index you add is a covering index for your query, you will get a small improvement by adding an index. Due to the smaller width of the index, a full scan of the index will be faster than a full scan of the entire table.

Upvotes: 4

Related Questions