mysqlmysql-dependent-subquery

MySQL: very slow query using nested sub-query


QUERY:

 SELECT 
    (
        SELECT COUNT(cleanpoi.ID) FROM cleanpoi
        WHERE cleanpoi.EstablishmentID=parent.ID
    ) AS POIs,
    (
        SELECT COUNT(ID) FROM cleanamenitymappings WHERE CleanPOIID IN 
        (
            SELECT cleanpoi.ID FROM cleanpoi
            WHERE cleanpoi.EstablishmentID=parent.ID
        )
    ) AS Amenities,
    (
        SELECT COUNT(ID) FROM cleanamenityvalues WHERE CleanPOIID IN 
        (
            SELECT cleanpoi.ID FROM cleanpoi
            WHERE cleanpoi.EstablishmentID=parent.ID
        )
    ) AS AmenityValues
    FROM establishment parent
    WHERE parent.ID=3

EXPLAIN RESULT:

 id  select_type         table                 type             possible_keys                              key              key_len  ref       rows  Extra                     
------  ------------------  --------------------  ---------------  -----------------------------------------  ---------------  -------  ------  ------  --------------------------
     1  PRIMARY             parent                const            PRIMARY                                    PRIMARY          4        const        1  Using index               
     5  DEPENDENT SUBQUERY  cleanamenityvalues    ALL              (NULL)                                     (NULL)           (NULL)   (NULL)   31778  Using where               
     6  DEPENDENT SUBQUERY  cleanpoi              unique_subquery  PRIMARY,EstablishmentID_2,EstablishmentID  PRIMARY          4        func         1  Using where               
     3  DEPENDENT SUBQUERY  cleanamenitymappings  index            (NULL)                                     CleanPOIID       4        (NULL)  673591  Using where; Using index  
     4  DEPENDENT SUBQUERY  cleanpoi              unique_subquery  PRIMARY,EstablishmentID_2,EstablishmentID  PRIMARY          4        func         1  Using where               
     2  DEPENDENT SUBQUERY  cleanpoi              ref              EstablishmentID_2,EstablishmentID          EstablishmentID  4        const      181     

Any advice with this query? Your answers are really appreciated!


Solution

  • Thanks for all the answers but this solved my problem.

    SELECT 
        (
            SELECT COUNT(cleanpoi.ID) FROM cleanpoi
            WHERE cleanpoi.EstablishmentID=parent.ID
        ) AS POIs,
        (
            SELECT COUNT(a.ID) 
            FROM cleanamenitymappings a LEFT JOIN cleanpoi b ON a.CleanPOIID=b.ID
            WHERE b.EstablishmentID=parent.ID
        ) AS Amenities,
        (
            SELECT COUNT(a.ID) 
            FROM cleanamenityvalues a LEFT JOIN cleanpoi b ON a.CleanPOIID=b.ID
            WHERE b.EstablishmentID=parent.ID
        ) AS AmenityValues
        FROM establishment parent
        WHERE parent.ID=3