MySQL optimization on filtering key-value pairs as records

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.

+----+----------------------
| 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".

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开发者_JAVA技巧 single query, with a limited amount or no changes to the stored data structure?

The working query, equivalent to the one above but much better optimized, credits to 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

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.

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