MySQL: very slow query using nested sub-query

Question

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!

To improve performance, remove the nested query or increase your `innodb_buffer_pool_size` — Michael Benjamin, Mar 21 '13 at 06:44

score 0 · Answer 1 · answered Mar 21 '13 at 06:38

Your query does scan through 700k rows, all which need to be read from disk. I am guessing on poor disk performance (bandwidth and/or seek time).

If you run this query often, consider creating a materialized view, which is a table holding the answer to the entire query and which you update using triggers when the underlying data changes.

score 0 · Answer 2 · answered Mar 21 '13 at 06:45

I would create temporary tables with the intermediate results, and then select from this temporary tables.

It might be useful to create a stored procedure to do that:

delimiter ;;
create procedure MyLongProcedure()
begin
    -- Just in case, drop the temporary tables before creating them
    drop table if exists temp_step01;
    -- Make a temporary table for each subquery
    create temporary table temp_step01
         select ... ;
    -- Be sure to create the appropriate indexes for each temporary table
    alter table temp_step01
         add index .... ;
    -- Create as much tables as you need
    -- Finally, execute your final query using all the tables you just created
    select ... ;
end;;
delimiter ;

Remember: Temporary tables can be read only by the connection that created them. In a multi-user environment that can be useful... or not, depending on your specific needs.

This is just an idea. Hope it helps you.

This might be helpful. I'll try this sooner. I'm still looking at the better solution. — hodl, Mar 21 '13 at 07:19

score 0 · Accepted Answer · answered Mar 21 '13 at 10:31

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

MySQL: very slow query using nested sub-query

3 Answers3