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