Improving SQL LIKE Performance

Question

We have a large table with product information. Almost all the time, we need to find product names that contain specific words, but, unfortunately, these queries are always satisfied.

Example: find all products in which the name contains the words “steel” and “102” (not necessarily next to each other, so a product like “Steel Iron Ninja 102 x” is a match like “dragon steel" 102 b " this is).

We are currently doing this as follows:

SELECT columns FROM products WHERE name LIKE '%WORD1%' AND name LIKE '%WORD2%' (the number of such words is usually 2-4, but theoretically it can be 7-8 or more).

Is there a faster way to do this?

We are only matching words, so I wonder if this can help in some way (that is, the products in the above example are the same, but “Samurai swordsteel 102 v” does not match, because “steel” is not alone).

My own thought is to create an auxiliary table with words from the product names, and then use this table to get the identifiers of the corresponding products.

i.e. a table like: [id, word, productid], so we get, for example:

 1, samurai, 3 2, swordsteel, 3 3, 102, 3 4, v, 3 

Just wondering if there is a built-in way to do this in MySQL, so I don't need to implement my own things + support two tables.

Thanks!

Answer 1

Unfortunately, you have wild cards at the beginning of the pattern name. Therefore, MySQL cannot use the standard index for this.

You have two options. First, if the words are really keywords / attributes, then you should have another table with one row per word.

If it is not, you can try the full text index. Please note that MySQL has attributes for minimum word length and uses a stop word list. You must consider this before creating an index.