mysql - One field search for multiple values -

What is the best and customized way to write a SQL query to search for a word with multiple columns in a table?

For example, I have a table of products:

  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 a table of colors:  
  id | Color ---------- 1 | Red 2 Green 3 Blue   
 and if a user enters the word  red dress , then the result should be seen with the  product  id Enter  1 , and if the user enters only  Red , then as a result, call it as  product  id  1   4 .  
  Update:  There may be some code like  dress red  or  red blue .  
 The actual version of tables is more complex, but I tried to explain it in a simple way.   
 
 
 A simple solution that "works" may be:  
 < COLOR.ID = PRODUCTS.color_id WHERE (title = "red" and color = "dress") or (color = "red" and title = "dress") on the color of JOIN colors from the code> SELECT * products   
 This condition can perform better, if the adapter is dumb enough not to notice it yourself:  
  WHERE (title = "red" or title = "dress ") And (color =" red "or color =" costume ")   
 If you get the problem (" A "and" Colors "), and if and if storage is not a problem, you can merge (and duplicate) all text properties in a single  VARCHAR  Columns, and this column.  
  Make products of products (Products Iid INT not original primary key, properties VARCHAR (255), foreign key fk_product (product_id) reference product (ID), FULLTEXT ftx_properties (attribute));   
 The search becomes very simple:  
  SELECT products. *, Colour. * Products from JOIN colors on colors.id = products.color_id JOIN products_properties AS pp.product_id = products.id at PP where "(+ + red + dress") against match (property)   
 This clearly does not make sense in this particular example, but more properties, the more it will give the speed of the query. This denormalization also comes at the cost of an increased complexity to maintain the  products_properties  table. .  
 Now the problem is really hairy if you  
 
 to deal with less commonized input such as "red blue"  
 related to both categories (For example, if "red" was a legitimate cloth headline) and still want to discriminate them (eg.   
 But it seems that this is the subject of your question Is out.