CODEWITHSUNDEEP
mysqlsql
Joe
Joe

Reputation: 415

Speed up MYSQL LIKE '%word%' statement

Current Query:

SELECT * FROM 'fnx_sports' WHERE (Title LIKE '%base%' OR Title LIKE '%ball%')

The above query will take 0.0300 seconds approx.

Faster Query:

SELECT * FROM 'fnx_sports' WHERE (Title LIKE 'base%' OR Title LIKE 'ball%')

The above query will take 0.0010 seconds approx.

The problem:

I need to the results from the first query, as the second (faster) query does not have the wildcard at the start (which allows for indexes to be used).

What options do I have?

One option is to have a separate table which contains all the keywords from the Title field, however this is not appropriate as I am looking to search on a character by character basis, not on a keyword by keyword basis.

For example, the following query should also work:

SELECT * FROM 'fnx_sports' WHERE (Title LIKE 'b%' OR Title LIKE 'b%' OR TitleReversed LIKE 'b%' OR TitleReversed LIKE 'b%')

I'm not sure if this is the most efficient way to do this is. I don't want to modify my table and code structure without confirming the best method. I appreciate any advice!

Note: Cannot use full-text since I have a table which gets quite large and needs constant inserts and selects, therefore I use INNODB instead of MYISAM which is required to use Full Text searches.

Upvotes: 1

Views: 2194

Answers (3)

acme
acme

Reputation: 14866

As you are using InnoDB and the option to switch to an external search engine (which is the best and fastest option) like Lucene or Sphinx was already mentionend here a different approach:

If the results don't need to show up immediately in your search you could run a cronjob periodically (e.g. once per hour) that inserts new rows in the InnoDB table into a separate table 'searchindex' which is MyISAM with fulltext index and is being used only for searching.

Upvotes: 1

Jaydee
Jaydee

Reputation: 4158

Are you always searching for whole words or at least the start of word?

If so you you could break up the words into a seperate table so "Base Ball Mania" would have three records in the table "Base", Ball", and "Mania", which could be indexed and searched.

Obviously this won't work if you are searching for "ball" and there is an entry for "baseball".

Upvotes: 0

Maxime Pacary
Maxime Pacary

Reputation: 23091

Maybe look at Lucene and Sphinx.

See:

How much more performant is Postgres than MYSQL on fulltext search?

(especially peufeu's answer)

Upvotes: 2

Related Questions