Search code examples
mysqlpartitioningsharding

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:

  • id - autoincrement,
  • code - alphanumeric code from 5 to 100 characters,
  • other fields.

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?

Solution

  • 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.