Query by two fields

Question

MySQL

Good afternoon!

I’ll say right away that there will be another perversion, but what can I do to work like this ... There is a table with products, I’m interested in the product name column (name_product), and there is a table with product characteristics (feature), a simple example: product “Brick”, characteristics “red ”, “yellow”, “opt”, etc.

It is necessary to write a search for this case ... with the possibility of relevance, i.e. if they asked for a red brick, then it should give out a brick with a characteristic first ...

I have only one thought, to denormalize everything into one table and look in it, but here a doubt arises, if the product is 100, and the characteristics of 100000 were found in only one product, then why iterate over all features. Maybe you can first search for the product in the query, if it was found in it, then issue it, if not, then search everywhere.

I came up with something like this (but I don't know in terms of resources):
SELECT CONCAT( `t1`.`name_goods`, IF( `t2`.`feature` != "", CONCAT( " ", `t2`.` feature` ), "" ) ) AS `name_goods`,
MATCH (
`name_goods`
)
AGAINST (
«thick facing hollow brick»
) AS relevance
FROM `im_goods` AS `t1`
INNER JOIN `im_records` AS `t2` ON `t2 `.`id_goods` = `t1`.`id_goods`
WHERE `name_goods` LIKE "%brick%thick%"
ORDER BY `relevance` DESC, `name_goods`

Answer 1

If it is possible to split the search query (brick red = "brick" and "red") then something like this

SELECT name_product, feature, IF(feature like '%красный%',100,0)
FROM table1 LEFT JOIN table2 ON ....
WHERE name_product = 'кирпич'
ORDER BY 3

Answer 2

Relevance here

Answer 3

If in this case the concept of "the number of matching feature names from the query" is considered relevant, then you can do something like this:
SELECT ...
FROM products
WHERE (name_product = 'brick' OR name_product = 'red')
ORDER BY (
SELECT count( *) FROM features WHERE product.id=features.p_id AND (name_feature = 'brick' OR name_feature = 'red')
True, this will probably not work very fast. Therefore, it seems to me that it is better to add their names to product field and search by text search

Answer 4

Or maybe it makes sense to make a normal filter by features instead of a search? depends on the site of course, but search is not always the best solution

Answer 5

If you need relevance and full-text search by characteristics, you can’t do without denormalization, MATCH can only be performed on an existing index. As a brake hack, you can use something like

SELECT items.name,GROUP_CONCAT(features.name),COUNT(features.name) 
FROM items LEFT JOIN features 
    ON items.id=features.item_id 
  AND (
    features.name='кирпич' OR features.name='утолщенный' 
    OR features.name='облицовочный' OR features.name='пустотелый'
  )

In the case of a large database, it is still better to use a third-party search engine like sphinx.