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

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?

CodePudding user response：

It seems that the join being used for the relation of the between objects and properties_relations is using the wrong name.

You are using alias for the properties_relations tables, while the actual table name is the one being used on the join condition.

This is the query that I have modified:

select
    *
from
    `objects`
    left join `properties_relations` as `properties_relations` on `objects`.`id` = `properties_relations`.`objectId`
        and `properties_relations`.`propertyId` = 43
    left join `properties_relations` as `properties_relations_b` on `objects`.`id` = `properties_relations_b`.`objectId`
        and `properties_relations_b`.`propertyId` = 44
    left join `properties_relations` as `properties_relations_c` on `objects`.`id` = `properties_relations_c`.`objectId`
        and `properties_relations_c`.`propertyId` = 45
    left join `properties_relations` as `properties_relations_d` on `objects`.`id` = `properties_relations_d`.`objectId`
        and `properties_relations_d`.`propertyId` = 46
where
    (
        and (`properties_relations`.`valueString` = "Red")
        and (`properties_relations_b`.`valueString` = "Monster Engine")
        and (`properties_relations_c`.`valueString` = "Blinker")
        and (`properties_relations_d`.`valueString` = "Roaring")
    )
    and `objects`.`deleted` = 0

In the above query, I have also moved the propertyId field from where condition to join condition. This is in order to reduce the rows being returned so that the query can be run faster.

CodePudding user response：

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;

CodePudding user response：

You are using AND Operand instead of OR. Since all those valueStrings are not on the same row. till eternity, it will return NULL because all those are not on the same row.