Hide Forgot
The following query, from beaker-usage-reminder's open_in_demand_systems routine, shows up in our slow query logs: Count: 834480 Time=0.02s (13407s) Lock=0.00s (75s) Rows_sent=0.0 (24569), Rows_examined=19369.8 (16163676217), beaker[beaker]@localhost SELECT reservation.start_time AS reservation_start_time, anon_1.waiting_recipes_count AS anon_1_waiting_recipes_count, system.fqdn AS system_fqdn FROM reservation INNER JOIN system ON system.id = reservation.system_id INNER JOIN (SELECT system.id AS id, count(system.id) AS waiting_recipes_count FROM system INNER JOIN system_recipe_map AS system_recipe_map_1 ON system.id = system_recipe_map_1.system_id INNER JOIN recipe ON recipe.id = system_recipe_map_1.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id INNER JOIN job ON job.id = recipe_se t.job_id WHERE recipe.status = 'S' AND recipe_set.queue_time <= 'S' AND job.deleted IS NULL GROUP BY system.id) AS anon_1 ON reservation.system_id = anon_1.id WHERE N = reservation.user_id AND reservation.start_time <= 'S' AND reservation.finish_time IS NULL The explain indicates it's using a temp table filesort for the derived table: MySQL [beaker_dcallagh]> explain SELECT reservation.start_time AS reservation_start_time, anon_1.waiting_recipes_count AS anon_1_waiting_recipes_count, system.fqdn AS system_fqdn FROM reservation INNER JOIN system ON system.id = reservation.system_id INNER JOIN (SELECT system.id AS id, count(system.id) AS waiting_recipes_count FROM system INNER JOIN system_recipe_map AS system_recipe_map_1 ON system.id = system_recipe_map_1.system_id INNER JOIN recipe ON recipe.id = system_recipe_map_1.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id INNER JOIN job ON job.id = recipe_set.job_id WHERE recipe.status = 'Queued' AND recipe_set.queue_time <= '2016-01-01 00:00:00' AND job.deleted IS NULL GROUP BY system.id) AS anon_1 ON reservation.system_id = anon_1.id WHERE 1104 = reservation.user_id AND reservation.start_time <= '2016-01-01 00:00:00' AND reservation.finish_time IS NULL; +----+-------------+---------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+-----------------------------------------------+------+------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+-----------------------------------------------+------+------------------------------------------------------------------+ | 1 | PRIMARY | reservation | index_merge | system_id,user_id,ix_reservation_start_time,ix_reservation_finish_time | user_id,ix_reservation_finish_time | 4,9 | NULL | 1 | Using intersect(user_id,ix_reservation_finish_time); Using where | | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 585 | Using where | | 1 | PRIMARY | system | eq_ref | PRIMARY | PRIMARY | 4 | beaker_dcallagh.reservation.system_id | 1 | | | 2 | DERIVED | recipe | ref | PRIMARY,recipe_set_id,status | status | 1 | | 314 | Using where; Using temporary; Using filesort | | 2 | DERIVED | system_recipe_map_1 | ref | PRIMARY,recipe_id | recipe_id | 4 | beaker_dcallagh.recipe.id | 8 | Using index | | 2 | DERIVED | system | eq_ref | PRIMARY | PRIMARY | 4 | beaker_dcallagh.system_recipe_map_1.system_id | 1 | Using index | | 2 | DERIVED | recipe_set | eq_ref | PRIMARY,job_id | PRIMARY | 4 | beaker_dcallagh.recipe.recipe_set_id | 1 | Using where | | 2 | DERIVED | job | eq_ref | PRIMARY,ix_job_deleted | PRIMARY | 4 | beaker_dcallagh.recipe_set.job_id | 1 | Using where | +----+-------------+---------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+-----------------------------------------------+------+------------------------------------------------------------------+ 8 rows in set (0.02 sec) We can simplify the query to use a dependent subquery instead, which gets us a nicer-looking EXPLAIN (although it might still end up in the slow query log due to some parts not using indexes): MySQL [beaker_dcallagh]> explain SELECT system.fqdn, reservation.start_time, (SELECT COUNT(*) FROM system_recipe_map INNER JOIN recipe ON system_recipe_map.recipe_id = recipe.id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id INNER JOIN job ON job.id = recipe_set.job_id WHERE system_recipe_map.system_id = system.id AND recipe.status = 'Queued' AND recipe_set.queue_time <= '2016-01-01 00:00:00' AND job.deleted IS NULL) AS waiting_recipes_count FROM system INNER JOIN reservation ON reservation.system_id = system.id AND reservation.finish_time IS NULL WHERE reservation.user_id = 1104 AND reservation.start_time <= '2016-01-01 00:00:00'; +----+--------------------+-------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+---------------------------------------------+------+------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+---------------------------------------------+------+------------------------------------------------------------------+ | 1 | PRIMARY | reservation | index_merge | system_id,user_id,ix_reservation_start_time,ix_reservation_finish_time | user_id,ix_reservation_finish_time | 4,9 | NULL | 1 | Using intersect(user_id,ix_reservation_finish_time); Using where | | 1 | PRIMARY | system | eq_ref | PRIMARY | PRIMARY | 4 | beaker_dcallagh.reservation.system_id | 1 | | | 2 | DEPENDENT SUBQUERY | system_recipe_map | ref | PRIMARY,recipe_id | PRIMARY | 4 | beaker_dcallagh.system.id | 6 | Using index | | 2 | DEPENDENT SUBQUERY | recipe | eq_ref | PRIMARY,recipe_set_id,status | PRIMARY | 4 | beaker_dcallagh.system_recipe_map.recipe_id | 1 | Using where | | 2 | DEPENDENT SUBQUERY | recipe_set | eq_ref | PRIMARY,job_id | PRIMARY | 4 | beaker_dcallagh.recipe.recipe_set_id | 1 | Using where | | 2 | DEPENDENT SUBQUERY | job | eq_ref | PRIMARY,ix_job_deleted | PRIMARY | 4 | beaker_dcallagh.recipe_set.job_id | 1 | Using where | +----+--------------------+-------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+---------------------------------------------+------+------------------------------------------------------------------+ 6 rows in set (0.00 sec) This version of the query is pulling systems joined to their open reservations with a subquery for counting the number of waiting recipes. We need to remember to filter out rows with 0 waiting recipes in Python land (that's done in SQL land in the current version of the query, but MySQL can't reference a dependency subquery in a WHERE condition so we can't do it in this proposed new version).