Improving a SQL LIKE query 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 take forever to run.

Example: Find all the products where the name contains the words "steel" and "102" (not necessarily next to each other, so a product like "Ninja steel iron 102 x" is a match, just like "Dragon steel 102 b" is it).

Currently we are doing it like this:

SELECT columns FROM products WHERE name LIKE '%WORD1%' AND name LIKE '%WORD2%' (the number of like words are normally 2-4, but it can in theory be 7-8 or more).

Is there a faster way of doing this?

We are only matching words, so I wonder if that can help somehow (i.e. the products in the example above are matches, but "Samurai swordsteel 102 v" is not a match since "steel" doesn't stand alone).

My own thought is to make a helper table with the words from productnames in and then use that table to get the ids of the matching 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 wonder if there is a built in way to do this in MySQL, so I don't have to implement my own stuff + maintain two tables.

Thanks!

Answer 1

Unfortunately, you have wild cards at the beginning of the pattern name. Hence, MySQL cannot use a 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 that is not the case, you can try a full text index. Note that MySQL has attributes for the minimum words length and uses a stop words list. You should take these into account before building the index.