tobi / delayed_job

Database backed asynchronous priority queue -- Extracted from Shopify
http://tobi.github.com/delayed_job
MIT License
2.15k stars 1.25k forks source link

efficient update job query #66

Open ghazel opened 8 years ago

ghazel commented 8 years ago

The query, which can take several seconds:

UPDATE `delayed_jobs` SET `delayed_jobs`.`locked_at` = '2016-02-17 05:20:59', `delayed_jobs`.`locked_by` = 'host:mine pid:60282' WHERE ((run_at <= '2016-02-17 05:20:59.484088' AND (locked_at IS NULL OR locked_at < '2015-02-17 05:20:59.484102') OR locked_by = 'host:mine pid:60282') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 1

My indexes:

Create Table: CREATE TABLE `delayed_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `priority` int(11) NOT NULL DEFAULT '0',
  `attempts` int(11) NOT NULL DEFAULT '0',
  `handler` text NOT NULL,
  `last_error` text,
  `run_at` datetime DEFAULT NULL,
  `locked_at` datetime DEFAULT NULL,
  `failed_at` datetime DEFAULT NULL,
  `locked_by` varchar(191) DEFAULT NULL,
  `queue` varchar(191) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `delayed_jobs_priority` (`priority`,`run_at`),
  KEY `index_delayed_jobs_on_priority_and_run_at` (`priority`,`run_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1154052 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED

EXPLAIN says:

+----+-------------+--------------+-------+---------------+---------+---------+------+-------+-----------------------------+  
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows  | Extra                       |  
+----+-------------+--------------+-------+---------------+---------+---------+------+-------+-----------------------------+  
|  1 | SIMPLE      | delayed_jobs | index | NULL          | PRIMARY | 4       | NULL | 57281 | Using where; Using filesort |  
+----+-------------+--------------+-------+---------------+---------+---------+------+-------+-----------------------------+  
1 row in set (0.00 sec)