MySQL Forums
Forum List  »  Performance

Eliminate filesort in update query
Posted by: Matzz -
Date: September 23, 2014 02:48AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Eliminate filesort in update query
2470
September 23, 2014 02:48AM
1002
September 23, 2014 09:59PM
932
September 24, 2014 01:25AM
977
September 24, 2014 08:40PM
975
September 25, 2014 05:48AM
916
September 26, 2014 04:39PM
967
October 01, 2014 06:49AM
982
October 03, 2014 10:09PM
1015
October 04, 2014 03:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.