CODEWITHSUNDEEP
mysqlsql
SkyDrive
SkyDrive

Reputation: 1455

improve performance for LIKE clause

This has been a pain on my head for the past few days. I created the database before without any knowledge about the performance of LIKE. The query I used is like this,

SELECT  ....
FROM    .... JOINS ....
WHERE   tableA.col1 LIKE '%keyword%' OR
        tableB.col2 LIKE '%keyword%' OR
        tableC.col2 LIKE '%keyword%' OR
        .....

When I tested the query, it was very fast because there was only around 100-150 records on it. I wanted to search for any string which contains the keyword. As months have past, the database grew huge containing 50,000 records. And this time, I already experiencing the low performance of the query. It was extremely low.

Any suggestions how can I improve it? I can't alter the database because it has already been used by the corporation already.

By the way, my tables were all INNODB.

Upvotes: 6

Views: 297

Answers (3)

mostafa.S
mostafa.S

Reputation: 1584

If you cannot use Full Text Search as other friends mentioned, there's a SQL optimization point that could improve your query:

SELECT  ....
FROM    (SELECT * FROM tableA WHERE col1 LIKE '%keyword%') A JOIN 
        (SELECT * FROM tableB WHERE col2 LIKE '%keyword%') B ON ... JOIN
        (SELECT * FROM tableC WHERE col2 LIKE '%keyword%') C ON ...

The point is to narrow down your resultsets prior to any join.

Upvotes: 0

Chris Pitman
Chris Pitman

Reputation: 13104

This type of search is call Full Text Search, and you really need to use specialized systems for it instead of forcing the database to constantly do table scans. You essnetially hand off all the text you want searched to a search engine, which then indexes it for quick search.

One option would be Apache Lucene.

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300797

Using a wildcard prefix '%abc' will very likely stop any indexes being used.

No index = full table scan = (usually) slow...

Btw, 50,000 records is not huge; it is tiny.

Have you considered using MySql's Full-Text Search Functions? (requires MyISAM tables)

Upvotes: 1

Related Questions