Geek Answers Handbook

Increase Productivity for LIKE Offer

It has been a pain on my head for the past few days. I created the database before without knowing about LIKE performance. The query I used is as follows:

 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 it only had about 100-150 records. I wanted to find any string containing a keyword. After several months, the database grew enormous, containing 50,000 records. And this time I am already experiencing poor query performance. It was extremely low.

Any suggestions how I can improve it? I cannot change the database because it has already been used by the corporation.

By the way, my tables were INNODB .

+6
source share
3 answers

This type of search is a Full-text search call, and you really need to use specialized systems for it, and not to force the database to constantly scan the table. You essentially transmit all the text you want to find in the search engine, which then indexes it for quick search.

One option: Apache Lucene .

+1
source

Using the wildcard prefix '%abc' is likely to stop any indexes used.

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

Btw, 50,000 records are not huge; it's tiny.

Do you find using MySql Full Text Search features ? (MyISAM table required)

+1
source

If you cannot use full-text search as the other friends mentioned, there is an SQL optimization point that can 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 bottom line is to narrow down the results to any connection.

0
source

More articles:


All Articles