Question 113508 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

3

Answers


  1. 0 
    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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search