我有一个表格,有3列和数百万行。所有都是整数(哈希)
id, attribute, attrib_val
ID可以具有许多行,并具有属性名称和值的组合。
桌子有两个键 id, attribute, attrib_val
attribute, attrib_val, id
我需要动态构建,可以根据规则获取ID的查询:
ID 所有以下各节都应匹配:
attribute <x> contains value <y> or <t>
attribute <l> does not contain value <f> or <c>
...
IDS 以下各节的任何应匹配:
attribute <x> contains value <y> or <t>
attribute <l> does not contain value <f> or <c>
...
问题:这是我想到的查询(我可以更改为不包含零件的ID,然后将其更改为或更改为或更改:
SELECT distinct id FROM attributes
WHERE id IN (
SELECT id FROM attributes
WHERE ( (attribute = 12944489 AND attrib_value = 907348202 )
)
AND id IN (
SELECT id FROM attributes
WHERE (
(attribute = 577513892 AND attrib_val = 519655334 )
OR (attribute = 577513892 AND attrib_val = 1266247963 )
)
)
)
问题在于此查询不是有效的。由于某种原因,MySQL扫描所有表行,如果我单独运行每个子查询,则包含几行行。
如何优化此查询或提出可以有效处理灵活要求的替代性。注意:1。MySQL 5.5.31 2.我简化了查询以简化解释。实际上,还有一个附加的全局SID列,所有查询都包含sid = xxx,每个段中的sid = xxx。
我建议使用 group by
和 having
:
SELECT id
FROM attributes
WHERE (attribute, attrib_value) IN ( (12944489, 907348202), (577513892, 519655334), (577513892, 1266247963) )
GROUP BY id
HAVING SUM( (attribute, attrib_value) IN ( (12944489, 907348202) ) ) > 0 AND
SUM( (attribute, attrib_value) IN ( (577513892, 519655334), (577513892, 1266247963) ) ) = 0;
SELECT id
FROM a AS a1
WHERE attr = 11 AND val IN (22, 33)
AND NOT EXISTS (
SELECT 1 FROM a
WHERE id = a1.id
AND attr = 44
AND val IN (55, 66) )
PRIMARY KEY(id) -- Is this already there? If so, good for inner query
INDEX(attr, val, id) -- needed for outer query