0

I couldn't seem to find a good posting on this topic. I have a MYSQL database that is probably 25M+ rows. There is one column that contains text strings (limit of 400 characters).

People would time to time do full wildchar searches on this column using LIKE '%pattern1%' or LIKE '%pattern2' where pattern1,pattern2 can be any type of word character of varying length.

The search times on this database with these queries take on average 20-30 minutes which I think is unacceptable

Any suggestions on how to improve the search times?

Gordon
  • 1,633
  • 3
  • 26
  • 45

1 Answers1

0

Good Day. 25M+ rows definitely a big number. It would be great, if you could have showed the schema of that particular table. Some suggestion:

  1. Always use index column (Primary Key) which will eventually increase performance.
  2. LIKE is a very powerful tool but it is slow. As an alternative you may want to consider Boolean Full-Text Searches The Boolean mode is faster than the LIKE operator for text searches. In the Boolean mode, MySQL searches for words instead of the concept like in the natural language search. MySQL allows you to perform a full-text search based on very complex queries in the Boolean mode along with Boolean operators.

Hope this helps.

Sujan
  • 21
  • 7