mysql - 优化属性值数据库中的 Mysql 查询

Question

我有一个数据库，但我预见到速度问题。因为我所有的数据都是单独保存的

Product table:
id int auto_inc PRI
name var_char(64)

attribute_table:
attr_id int auto_inc PRI
name var_char(64)

attribute_value:
attr_id int
product_id
value

但是当我进行高级搜索时，我会得到像

这样的大量查询

select * from product_table  as pt
cross join attribute_value as av ON av.product_id = pt.id AND av.value LIKE "test"
cross join attribute_table as at ON at.attr_id = av.attr_id AND at.name = "test"
cross join attribute_value as av ON av.product_id = pt.id AND av.value LIKE "test2"
cross join attribute_table as at ON at.attr_id = av.attr_id AND at.name = "test2"
cross join attribute_value as av ON av.product_id = pt.id AND av.value LIKE "test3"
cross join attribute_table as at ON at.attr_id = av.attr_id AND at.name = "test3"

但我猜这不是真的快。但有没有办法优化？或者我可以使用一些技巧来更快地搜索，比如一种兑现？

Answer 1

我相信你想要更像这样的东西:

select * from product_table as pt JOIN attribute_value as av 
    ON av.product_id = pt.id
WHERE (av.value LIKE "test") 
    OR (at.name = "test") 
    OR (av.value LIKE "test2") 
    OR (at.name = "test2") ....

这会让您获得一个连接，并从结果中进行选择。我认为这更接近您想要的。