Eliminate sorting mysql file in update request

I have a table like this which I use to implement a 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 mysql is using filesort when the update starts. Execution is very slow (5s for 800k rows in the 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's strange is when I run a SELECT query with the same WHERE clauses and ORDER columns the 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 |
+ ---- + ------------- + ------- + ------ + --------------- + ------------- + --------- + ------------- + -------- + - ------------------------ +

(Request time 0s)

Does anyone know how to avoid using filesort in an update request, or how to improve its performance?

Regards, Matzz

+3


source to share


1 answer


After discussion on the mysql forum ( http://forums.mysql.com/read.php?24,620908,620908#msg-620908 ) I reported a bug http://bugs.mysql.com/bug.php?id= 74049 (which has been tested). The problem can be worked around with 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;

      

0


source







All Articles