MySQL : Timeout when selecting records with conditions that applies to multiple rows

Viewed 122

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?

1 Answers

select * from objects left join properties_relations as properties_relations on objects.id = properties_relations.objectId where properties_relations.valueString IN ("Red","Monster Engine") and objects.deleted = 0;

Related