Clever Geek Handbook

Partitioning a large MySQL table using LIKE for lookups

I have a table with 80 million records. Table structure:

  • id - auto-increment,
  • code - an alphanumeric code from 5 to 100 characters,
  • other fields.

Most commonly used query

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

The number of requests is growing as well as the number of recodrs. I will have performance issues very soon.

Is there a way to split the table into parts? Or maybe some other ways to optimize the table?

+3


source to share


2 answers


The %

killer here is leading the search. This negates the use of any index.

The only thing I can think of is to split the table by code length.

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

This saves you from searching all codes less than 10 characters long, which will never match. Alternatively, you can use the index for one of the searches (the first one).



It will also help reduce the size of the tables.

You can use UNION

to execute all queries at the same time, although you probably want to create the query dynamically.

You should also see if FULLTEXT might not index a better solution.

+2


source


Some thoughts:

  • You can split a table into multiple smaller tables based on a specific condition. For example, there ID

    may or may be code

    or may be any other fields. This basically means that you keep a specific type of records in a table and split different types into different tables.

  • Try Splitting MySQL

  • If it is possible. clean up old entries or you can at least consider moving them to another archive table

  • Instead of LIKE, consider using REGEXP to find regular expressions

  • Instead of running, SELECT *

    try selecting only selectable columnsSELECT id, code, ...

  • I'm not sure if this query is somewhat related to a search in your application where the value entered by the user is compared to a column code

    and the results are repeated for the user. But if so, you can try adding parameters to the search query, for example asking the user if they want an exact match or should start with a match, etc. This way, you don't have to run LIKE matching every time.

  • This should have been the first, but I am assuming you have the desired indexes on the table

  • Try to use more request cache. The best way to use this is to avoid frequent updates to the table, because the query cache is cleared on every update. The fewer updates, the more likely MySQL is caching queries, which means faster results



Hope it helps!

0


source






More articles:


All Articles