One field search for multiple values

Question

What is the best and optimized way to write an SQL query to search for a term with multiple columns in a table?

For example, I have a product table:

id | title | color_id ------------------- 1 | Dress | 1 (red) 2 | T-shirt | 3 (blue) 3 | Pants | 2 (green) 4 | Socks | 1 (red) 5 | Dress | 2 (green) 6 | Shoes | 2 (green) 7 | Pants | 3 (blue) 

And the color chart:

 id | color ---------- 1 | Red 2 | Green 3 | Blue 

And if the user enters the term Red dress , as a result, he should see Product with id 1 , and if the user enters simply Red , as a result (he) he should see Products with id 1 and 4 .

Update: There may be some inputs, such as dress red or red blue .

The actual version of the tables is more complicated, but I tried to explain in the simplest way.

Answer 1

A simple solution that just “works” might be:

 SELECT * FROM products JOIN colors ON colors.id = products.color_id WHERE ( title = "red" AND color = "dress" ) OR ( color = "red" AND title = "dress" ) 

This condition may work better if the optimizer is dumb enough not to notice it on its own:

 WHERE ( title = "red" OR title = "dress") AND ( color = "red" OR color = "dress") 

If you add more properties to the problem (besides "title" and "color"), and if and if the repository is not a problem, you may want to combine (and duplicate) all text properties into one VARCHAR and run a full-text search in this column.

 CREATE TABLE products_properties ( product_id INT NOT NULL PRIMARY KEY, properties VARCHAR (255), FOREIGN KEY fk_product (product_id) REFERENCES products(id), FULLTEXT ftx_properties (properties) ); 

The search becomes very simple:

 SELECT products.*, colors.* FROM products JOIN colors ON colors.id = products.color_id JOIN products_properties AS pp ON pp.product_id = products.id WHERE MATCH(properties) AGAINST ("+red +dress") 

This clearly does not make sense in this particular example, but the more properties, the more it will speed up the request. This denormalization is also associated with increased complexity for maintaining the products_properties table.

Now the problem gets really hairy if you

• should deal with less normalized inputs like red blue
• have words that belong to both categories (for example, if “red” was also the right fabric) and still want to distinguish between them (for example, a query like “red blue” returns a fabric named “red” with the color “blue”)

But it seems like this is beyond the scope of your question.

Answer 2

If I did this in SQL, I would usually split the query into separate words before executing the query, and then build the query dynamically based on how many words there were.

So, for your example, the query might look like this:

 SELECT * FROM products p JOIN colors c ON c.id = p.color_id WHERE p.title LIKE '%red%' OR c.color LIKE '%red%' OR p.title LIKE '%dress%' OR c.color LIKE '%dress%' 

If you have many tables, this can become quite complicated. It is also not very effective, since hardly any indexes will be used.

The best solution would be to use a special product for indexing text such as Lucene (but this is the whole question) ..

Answer 3

My idea is to use a query like this:

 set @search = 'red dress'; SELECT * FROM products INNER JOIN colors ON products.color_id = colors.id WHERE (FIND_IN_SET(title, REPLACE(@search, ' ', ','))>0)+ (FIND_IN_SET(color, REPLACE(@search, ' ', ','))>0) = LENGTH(@search)-LENGTH(REPLACE(@search, ' ', ''))+1; 

Two replacements inside FIND_IN_SET are used to create a list of properties separated by commas:

 red,dress 

Then I check to see if there is a title in this set. If present, the value is:

 (FIND_IN_SET(title, REPLACE(@search, ' ', ','))>0) 

will be evaluated at 1, 0 otherwise. The same goes for color .

The number of properties in the search bar can be calculated as:

 LENGTH(@search)-LENGTH(REPLACE(@search, ' ', ''))+1 

(yes, this is a dirty trick!). If the number of matching properties matches the properties in the @search string, the string will be returned. Please note that performance will be poor.

Fiddle is here .