Bug 1079093

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.