In a large database, I get a timeout as soon as I combine multiple left joins to find objects that have multiple rows with matching conditions.
I want to find all objects that have in a different table the property "Red", "Monster Engine", "Blinker" and "Roaring" set.
So I have a MYSQL table with contact Objects called "objects":
| id |deleted|
===============
| 1 | false |
| 2 | false |
| 3 | false |
Each object has properties stored in another table "properties_relations"
| id | objectId | propertyId | valueString |
=======================================================
| 12 | 1 | 43 | Red |
| 13 | 1 | 44 | Monster Engine|
| 14 | 1 | 45 | Blinker |
| 15 | 1 | 46 | Roaring |
Now I want to select all Objects that have a property with the value "Red" and the value "Monster Engine".
I did it this way according to this post MySQL : Select records with conditions that applies to multiple rows:
select * from `objects`
left join `properties_relations` as `properties_relations` on `objects`.`id` = `properties_relations`.`objectId`
left join `properties_relations` as `properties_relations_b` on `objects`.`id` = `properties_relations`.`objectId`
where (`properties_relations`.`propertyId` = 43 and (`properties_relations`.`valueString` = "Red")
and `properties_relations_b`.`propertyId` = 44 and (`properties_relations_b`.`valueString` = "Monster Engine")
)
and `objects`.`deleted` = 0
This his, however, working. But as soon as I add a third or fourth condition I will get in a timeout. I saw that the number of rows is increasing exponentially the more joins I add.
The query that is NOT working looks like this:
select * from `objects`
left join `properties_relations` as `properties_relations` on `objects`.`id` = `properties_relations`.`objectId`
left join `properties_relations` as `properties_relations_b` on `objects`.`id` = `properties_relations`.`objectId`
left join `properties_relations` as `properties_relations_c` on `objects`.`id` = `properties_relations`.`objectId`
left join `properties_relations` as `properties_relations_d` on `objects`.`id` = `properties_relations`.`objectId`
where (`properties_relations`.`propertyId` = 43 and
(`properties_relations`.`valueString` = "Red")
and `properties_relations_b`.`propertyId` = 44 and (`properties_relations_b`.`valueString` = "Monster Engine")
and `properties_relations_c`.`propertyId` = 45 and (`properties_relations_c`.`valueString` = "Blinker")
and `properties_relations_d`.`propertyId` = 46 and (`properties_relations_d`.`valueString` = "Roaring")
)
and `objects`.`deleted` = 0
So what can I do about this?