Bug 1062469

Specifically we should examine the following query from mysqld slow query log, which appears to be from labcontroller.get_queued_commands:

SELECT command_queue.status AS command_queue_status, [...]
FROM activity INNER JOIN command_queue ON activity.id = command_queue.id INNER JOIN system ON system.id = command_queue.system_id 
WHERE 8 = system.lab_controller_id AND command_queue.status = 'Queued' ORDER BY activity.id;

to verify that it is indeed picking an expensive query plan (check EXPLAIN) and that adding the index fixes it (check EXPLAIN again after adding the index).

(In reply to Dan Callaghan from comment #1)

Adding the index on command_queue.status reduces the time for this query from 29 seconds down to 0.01 seconds in my testing.

EXPLAIN before:

+----+-------------+---------------+--------+---------------------------+-------------------+---------+----------------------------------+------+-----------------------------------------------------------+
| id | select_type | table         | type   | possible_keys             | key               | key_len | ref                              | rows | Extra                                                     |
+----+-------------+---------------+--------+---------------------------+-------------------+---------+----------------------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | system        | ref    | PRIMARY,lab_controller_id | lab_controller_id | 5       | const                            | 2225 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | command_queue | ref    | PRIMARY,system_id         | system_id         | 4       | beaker_dcallagh.system.id        |  696 | Using where                                               |
|  1 | SIMPLE      | activity      | eq_ref | PRIMARY                   | PRIMARY           | 4       | beaker_dcallagh.command_queue.id |    1 |                                                           |
+----+-------------+---------------+--------+---------------------------+-------------------+---------+----------------------------------+------+-----------------------------------------------------------+

EXPLAIN after:

+----+-------------+---------------+--------+---------------------------+---------+---------+-----------------------------------------+------+----------------------------------------------+
| id | select_type | table         | type   | possible_keys             | key     | key_len | ref                                     | rows | Extra                                        |
+----+-------------+---------------+--------+---------------------------+---------+---------+-----------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | command_queue | ref    | PRIMARY,system_id,status  | status  | 1       | const                                   |   25 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | system        | eq_ref | PRIMARY,lab_controller_id | PRIMARY | 4       | beaker_dcallagh.command_queue.system_id |    1 | Using where                                  |
|  1 | SIMPLE      | activity      | eq_ref | PRIMARY                   | PRIMARY | 4       | beaker_dcallagh.command_queue.id        |    1 |                                              |
+----+-------------+---------------+--------+---------------------------+---------+---------+-----------------------------------------+------+----------------------------------------------+

On Gerrit: http://gerrit.beaker-project.org/2834

if no regression bug is found in acceptance test for version 0.16, will move this bug to "VERIFIED"

Beaker 0.16.0 has been released.

*** Bug 1079094 has been marked as a duplicate of this bug. ***