improve performance for LIKE clause

Question

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.

Answer 1

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.

Answer 2

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)