collectiveidea/delayed_job_active_record — pull request #71 by spajus (2013-08-14 05:08:43+00:00)
Title: Add index on delayed_jobs (locked_at, locked_by)

This index avoids an increasingly growing performance penalty when delayed_jobs table tends to grow bigger.

Before index:
```
mysql> explain SELECT `delayed_jobs`.* FROM `delayed_jobs` WHERE `delayed_jobs`.`locked_at` = '2013-08-13 13:03:06' AND `delayed_jobs`.`locked_by` = 'delayed_job host:private.internal pid:12345' LIMIT 1;
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | delayed_jobs | ALL | NULL | NULL | NULL | NULL | 260546 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
```

After:
```
mysql> explain SELECT `delayed_jobs`.* FROM `delayed_jobs` WHERE `delayed_jobs`.`locked_at` = '2013-08-13 13:03:06' AND `delayed_jobs`.`locked_by` = 'delayed_job host:private.internal pid:12345' LIMIT 1;
+----+-------------+--------------+------+-------------------+-------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+-------------------+-------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | delayed_jobs | ref | delayed_jobs_lock | delayed_jobs_lock | 312 | const,const | 1 | Using where |
+----+-------------+--------------+------+-------------------+-------------------+---------+-------------+------+-------------+
```

Visual performance impact after this migration was applied to a production app during rush hour:
![preview](http://new.tinygrab.com/bd43eb7ea95c6a4eb72e94648064502f3ed39bb949.png)

Kudos to @tomas-didziokas for suggesting this fix.