mysql - 使用一张表进行多个选择查询(提高性能)

Question

有没有办法改进这个查询？

 SELECT variety_id FROM anvariety_trait WHERE variety_id IN
 (SELECT variety_id FROM anvariety_trait WHERE trait_id = 10 and trait_value =     2 and variety_id IN 
 (SELECT variety_id FROM anvariety_trait WHERE trait_id = 9 and trait_value = 1 and variety_id IN 
 (SELECT variety_id FROM anvariety_trait WHERE trait_id = 7 and trait_value = 1 )))

我试图从同一个表中获取三个级别的选择查询的一个结果。提前致谢。

Answer 1

您可以尝试连接子句和一个不同的，但我不确定这会提高您的性能(无论如何值得一试)

select distinct v1.variety_id 
FROM anvariety_trait v1
join anvariety_trait v2 on v1.variety_id = v2.variety_id
join anvariety_trait v3 on v2.variety_id = v3.variety_id
join anvariety_trait v4 on v3.variety_id = v4.variety_id
where v2.trait_id = 10 and v2.trait_value = 2
and   v3.trait_id = 9  and v3.trait_value = 1
and   v4.trait_id = 7  and v4.trait_value = 1