CODEWITHSUNDEEP
mysqlpartitioningsharding
Leksat
Leksat

Reputation: 3101

Partitioning of a large MySQL table that uses LIKE for search

I have a table with 80 millions of records. The structure of the table:

The most used query is

SELECT * FROM table
WHERE code LIKE '%{user-defined-value}%'

The number of queries is growing as well as the recodrs count. Very soon I will have performance problems.

Is there any way to split the table in the parts? Or maybe some other ways to optimize the table?

Upvotes: 3

Views: 597

Answers (2)

Abhay
Abhay

Reputation: 6645

Some thoughts:

  1. You can split the table into multiple smaller tables based on a certain condition. For example, on ID perhaps or may be code or may be any other fields. It basically means that you keep a certain type of records in a table and split different types into different tables

  2. Try MySQL Partitioning

  3. If possible. purge older entries or you may at least think of moving them to another archive table

  4. Instead of LIKE, consider using REGEXP for regular expression search

  5. Rather than running SELECT *, try selecting only selective columns SELECT id, code, ...

  6. I'm not sure if this query is somewhat related to a search within your application where a user inputted value is compared with the code column and results echoed to the user. But if yes, you can try to add options to the search query, like asking user if he wants an exact match or should start with match etc. This way you do not necessarily need to run a LIKE match everytime

  7. This should have been the first point, but I assume you have the right indexes on the table

  8. Try using more of the query cache. The best way to use it is to avoid frequent updates to the table because on each update the query cache is cleaned. So lesser the updates, more likely it is that MySQL caches the queries, which will then mean quicker results

Hope the above helps!

Upvotes: 0

Ami
Ami

Reputation: 1254

The leading % in the search is the killer here. It negates the use of any index.

The only thing I can think of is to partition the table based on length of code.

For example, if the code that is entered is 10 characters long, then first search the table with 10 character codes, without the leading percent sign, then search the table with 11 character codes, with the leading percent sign, and then the table with 12 character codes, with the leading percent sign, and so on.

This saves you from searching through all of codes that are less than 10 characters long that will never match. Also, you are able to utilize an index for one of the searches (the first one).

This also will help keep the table sizes somewhat smaller.

You can use a UNION to perform all of the queries at once, though you'll probably want to create the query dynamically.

You should also take a look to see if FULLTEXT indexing might be a better solution.

Upvotes: 2

Related Questions