The following query runs quite slowly. # Time: 140301 11:51:08 # User@Host: beaker[beaker] @ localhost [127.0.0.67] # Query_time: 18 Lock_time: 0 Rows_sent: 37 Rows_examined: 1046418 SELECT watchdog.id AS watchdog_id, watchdog.recipe_id AS watchdog_recipe_id, watchdog.recipetask_id AS watchdog_recipetask_id, watchdog.subtask AS watchdog_subtask, watchdog.kill_time AS watchdog_kill_time FROM watchdog INNER JOIN recipe ON recipe.id = watchdog.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id WHERE 11 = recipe_set.lab_controller_id AND watchdog.kill_time IS NOT NULL AND recipe_set.id IN (SELECT recipe_set.id AS recipe_set_id FROM recipe_set INNER JOIN recipe ON recipe_set.id = recipe.recipe_set_id INNER JOIN watchdog ON recipe.id = watchdog.recipe_id GROUP BY recipe_set.id HAVING max(watchdog.kill_time) > '2014-03-01 16:50:50');
An alternative and much faster query is the following: SELECT watchdog.id FROM watchdog INNER JOIN recipe ON recipe.id = watchdog.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id WHERE 9 = recipe_set.lab_controller_id AND watchdog.kill_time IS NOT NULL AND EXISTS (SELECT 1 FROM recipe AS r2 INNER JOIN watchdog ON watchdog.recipe_id = r2.id WHERE r2.recipe_set_id = recipe_set.id GROUP BY r2.recipe_set_id HAVING max(watchdog.kill_time) > '2014-03-01 16:50:50')
http://gerrit.beaker-project.org/#/c/3104/
For future reference: with this bug fix, the query Beaker produces is not exactly the same as the one given in comment 2. Specifically the GROUP BY and HAVING have been replaced with a WHERE clause. Here is an example: SELECT watchdog.id AS watchdog_id, watchdog.recipe_id AS watchdog_recipe_id, watchdog.recipetask_id AS watchdog_recipetask_id, watchdog.subtask AS watchdog_subtask, watchdog.kill_time AS watchdog_kill_time FROM watchdog INNER JOIN recipe ON recipe.id = watchdog.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id WHERE watchdog.kill_time IS NOT NULL AND (EXISTS (SELECT 1 FROM recipe INNER JOIN watchdog ON recipe.id = watchdog.recipe_id WHERE recipe.recipe_set_id = recipe_set.id AND watchdog.kill_time > '2014-06-11 22:37:15')) AND 15 = recipe_set.lab_controller_id; When checking the EXPLAIN output for this query it would be important to use a production database dump and a valid lab controller ID in the WHERE clause, in order to select a realistic number of rows.
Verified: With Dan's help, check the response with the new sql, it is faster than before: Old sql SELECT watchdog.id AS watchdog_id, watchdog.recipe_id AS watchdog_recipe_id, watchdog.recipetask_id AS watchdog_recipetask_id, watchdog.subtask AS watchdog_subtask, watchdog.kill_time AS watchdog_kill_time FROM watchdog INNER JOIN recipe ON recipe.id = watchdog.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id WHERE 8 = recipe_set.lab_controller_id AND watchdog.kill_time IS NOT NULL AND recipe_set.id IN (SELECT recipe_set.id AS recipe_set_id FROM recipe_set INNER JOIN recipe ON recipe_set.id = recipe.recipe_set_id INNER JOIN watchdog ON recipe.id = watchdog.recipe_id GROUP BY recipe_set.id HAVING max(watchdog.kill_time) < '2014-06-12 16:50:50'); +-------------+--------------------+------------------------+------------------+---------------------+ | watchdog_id | watchdog_recipe_id | watchdog_recipetask_id | watchdog_subtask | watchdog_kill_time | +-------------+--------------------+------------------------+------------------+---------------------+ | 888865 | 963015 | 14150462 | NULL | 2014-01-11 18:20:01 | ... +-------------+--------------------+------------------------+------------------+---------------------+ 289 rows in set (0.79 sec) New sql: SELECT watchdog.id AS watchdog_id, watchdog.recipe_id AS watchdog_recipe_id, watchdog.recipetask_id AS watchdog_recipetask_id, watchdog.subtask AS watchdog_subtask, watchdog.kill_time AS watchdog_kill_time FROM watchdog INNER JOIN recipe ON recipe.id = watchdog.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id WHERE watchdog.kill_time IS NOT NULL AND (EXISTS (SELECT 1 FROM recipe INNER JOIN watchdog ON recipe.id = watchdog.recipe_id WHERE recipe.recipe_set_id = recipe_set.id AND watchdog.kill_time < '2014-06-12 16:50:50')) AND 8 = recipe_set.lab_controller_id; +-------------+--------------------+------------------------+------------------+---------------------+ | watchdog_id | watchdog_recipe_id | watchdog_recipetask_id | watchdog_subtask | watchdog_kill_time | +-------------+--------------------+------------------------+------------------+---------------------+ | 888865 | 963015 | 14150462 | NULL | 2014-01-11 18:20:01 | ... +-------------+--------------------+------------------------+------------------+---------------------+ 289 rows in set (0.10 sec) 0.10 sec is better than 0.79 sec. Change it to verified
Beaker 0.17.1 has been released.