MySQL优化过滤键-值对作为记录

I've got a DB structure that is designed to store attributes for specific objects in an easily extendable manner.
There is an "Objects" table.

我有一个DB结构，它设计用来以一种易于扩展的方式存储特定对象的属性。有一个“对象”表。

+----+----------------------
| id | ....(name, type, etc)
+----+----------------------

Next, I have an "Attributes" table.

接下来，我有一个“属性”表。

+----+------+
| id | Name |
+----+------+

And finally, a "Relations" table, used to keep all the data as the attribute-object pairs (as a primary key) with the corresponding values.

最后，一个“关系”表，用于将所有数据作为属性对象对(作为主键)与相应的值保持一致。

+--------+---------+-------+
| id_obj | id_attr | value |
+--------+---------+-------+

I need to fetch IDs for objects that meet several conditions at once. For example, I have attributes named "Type" and "City", and I need to fetch IDs for the objects where the corresponding values for these attributes are "Apartment" and "City b".

我需要为同时满足几个条件的对象获取id。例如，我有名为“Type”和“City”的属性，我需要为这些属性对应值为“Apartment”和“City b”的对象获取id。

The best solution I managed to come up with after banging my head against the wall since yesterday (well, the only good thing about this query is that it actually works and fetches the records needed):

从昨天开始，我就设法想出了最好的解决方案(好吧，这个查询唯一的好处就是它确实有效，并且可以获取所需的记录):

SELECT objects.id
FROM (attributes INNER JOIN relations ON attributes.id = relations.id_attr) 
  INNER JOIN objects ON relations.id_obj = objects.id
WHERE objects.id
IN (
 SELECT objects.id
 FROM (attributes INNER JOIN relations ON attributes.id = relations.id_attr) 
  INNER JOIN objects ON relations.id_obj = objects.id
 WHERE attributes.name = 'Type' AND relations.value = 'Apartment'
)
AND objects.id
IN (
 SELECT objects.id
 FROM (attributes INNER JOIN relations ON attributes.id = relations.id_attr) 
  INNER JOIN objects ON relations.id_obj = objects.id
 WHERE attributes.name = 'City' AND relations.value = 'City b'
)
GROUP BY objects.id ASC
LIMIT 0 , 20

The thing is, the amount of data stored may potentially become somewhat large and I'm afraid that all these subqueries (there might be need to specify some 10-15 filters), each one parsing the whole DB, may cause serious performance issues (not to say that even with my limited SQL skills I'm pretty sure that there must be a better way of doing what I need to do).
On the other hand, drastic changes to the DB are not really an option since the code working with it depends heavily on the current DB structure.
Is there a way to check the attributes the way I need it in a single query, with a limited amount or no changes to the stored data structure?

事情是这样的,存储的数据量可能变得有点大,我担心这些子查询(可能需要指定有10 - 15过滤器),每一个解析整个数据库,可能会导致严重的性能问题(不是说即使我有限的SQL技能我很确定一定有更好的方法做我需要做的事情)。另一方面，对DB的剧烈更改实际上不是一个选项，因为使用它的代码在很大程度上依赖于当前的DB结构。是否有一种方法可以像我在单个查询中需要的那样，对存储的数据结构进行有限数量的检查，或者不进行任何更改?

---

The working query, equivalent to the one above but much better optimized, credits to DRapp :

工作查询，相当于上面的一个，但优化得更好，对DRapp的积分:

SELECT STRAIGHT_JOIN
      rel.id_obj
   from 
      relations rel
         join attributes atr
            on rel.id_attr = atr.id
   where 
         ( rel.value = 'Apartment' AND atr.name = 'Type'  )
      or ( rel.value = 'City b' AND atr.name = 'City' )
   group by 
      rel.id_obj
   having
      count(*) = 2
   limit
      0, 20

1 个解决方案

#1

This should get you what you need... Each of the "OR"d where clause conditions, you can just keep adding to as a qualified item. Then, just adjust the "Having" clause to meet the same number as criteria you are allowing... I've put the relations table first as that would have a smaller matched set on the "Value" of City or type values... Ensure you have an index on the relations table on the "VALUE" column.

这应该能让你得到你需要的……每个“或”d where子句条件，您可以继续添加为一个合格项。然后，只需调整“拥有”条款，以满足与您所允许的标准相同的数量。我将关系表放在前面，因为在城市或类型值的“值”上有一个较小的匹配集……确保在“VALUE”列的关系表上有索引。

SELECT STRAIGHT_JOIN
      rel.id_obj
   from 
      relations rel
         join attributes atr
            on rel.id_addr = atr.id
   where 
         ( rel.value = 'Apartment' AND atr.name = 'Type'  )
      or ( rel.value = 'Some City' AND atr.name = 'City' )
   group by 
      atr.id_obj
   having
      count(*) = 2
   limit
      0, 20

If you want all the actual object data FROM this results, you would wrap it something like...

如果您想要从这个结果中得到所有实际的对象数据，那么您可以将它包装成……

select obj.*
   from 
      ( complete SQL statement above ) PreQuery
         join Object obj on PreQuery.id_obj = obj.id