Eliminate mysql file sort in update query

Question

I have such table which I use to implement queue in mysql:

CREATE TABLE `queue` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `queue_name` varchar(255) NOT NULL,
  `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `inserted_by` varchar(255) NOT NULL,
  `acquired` timestamp NULL DEFAULT NULL,
  `acquired_by` varchar(255) DEFAULT NULL,
  `delayed_to` timestamp NULL DEFAULT NULL,
  `priority` int(11) NOT NULL DEFAULT '0',
  `value` text NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'new',
  PRIMARY KEY (`id`),
  KEY `queue_index` (`acquired`,`queue_name`,`priority`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

My problem is that mysql use filesort when I run update. Execution is very slow (5s for 800k rows in table).

DESCRIBE UPDATE queue SET acquired = "test" WHERE acquired IS NULL AND queue_name = "q1" ORDER BY priority, id LIMIT 1;

+----+-------------+-------+-------+---------------+-------------+---------+-------------+--------+-----------------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref         | rows   | Extra                       |
+----+-------------+-------+-------+---------------+-------------+---------+-------------+--------+-----------------------------+
|  1 | SIMPLE      | queue | range | queue_index   | queue_index | 772     | const,const | 409367 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+-------------+---------+-------------+--------+-----------------------------+

What is strange, when I run SELECT query with same WHERE conditions and ORDER columns filesort is not used:

DESCRIBE SELECT id FROM queue WHERE acquired IS NULL AND queue_name = "q1" ORDER BY priority, id LIMIT 1;

+----+-------------+-------+------+---------------+-------------+---------+-------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows   | Extra                    |
+----+-------------+-------+------+---------------+-------------+---------+-------------+--------+--------------------------+
|  1 | SIMPLE      | queue | ref  | queue_index   | queue_index | 772     | const,const | 409367 | Using where; Using index |
+----+-------------+-------+------+---------------+-------------+---------+-------------+--------+--------------------------+

(Query time 0s)

Does anybody know how avoid using filesort in update query or how increase its performance?

Regards, Matzz

Might be because of the PRIMARY KEY in the table. MySQL will check the key inegrity on each UPDATE. In MariaDB you can disable these checks, might work on MySQL, too (see here) — Benvorth

Matzz Matzz · Accepted Answer · 2014-09-25T19:06:52

After discussion at mysql forum (http://forums.mysql.com/read.php?24,620908,620908#msg-620908) I reported bug http://bugs.mysql.com/bug.php?id=74049 (which was verified). Issue could be bypassed using SELECT FOR UPDATE:

SET @update_id := -1;
SELECT (SELECT @update_id := id)
FROM queue
WHERE acquired IS NULL AND queue_name = "q1"
ORDER BY priority, id LIMIT 1;
FOR UPDATE;
UPDATE queue SET acquired = "test" WHERE id = @update_id;

Eliminate mysql file sort in update query

1 Answers