I am using MySQL 5.1.73 with InnoDB
I have a very slow UPDATE query (about 10s), where the number of changed rows is (often) zero (it takes about the same time if there are a few rows changed):
UPDATE job_queue SET state=4 WHERE error_counter>=5 AND state=1;
Query OK, 0 rows affected (9.33 sec)
However, the corresponding SELECT is quite fast:
SELECT id FROM job_queue WHERE error_counter>=5 AND state=1;
Empty set (0.03 sec)
EXPLAIN SELECT id FROM job_queue WHERE error_counter>=5 AND state=1;
+----+-------------+-----------+-------+--------------------------------------------+---------------------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+--------------------------------------------+---------------------+---------+------+-------+--------------------------+
| 1 | SIMPLE | job_queue | index | Allinone,state_timeout,state_error_counter | state_error_counter | 5 | NULL | 13515 | Using where; Using index |
+----+-------------+-----------+-------+--------------------------------------------+---------------------+---------+------+-------+--------------------------+
As you can see, this is (should be?) an indexed query, on a not-too-big database (about 9000 rows, although it does contain some blobs so the total DB size is about 800MB).
The questions are:
UPDATE: I tried the same database on a different server with MySQL 5.5.35 (Debian wheezy default). The query is still fast, and the update is still slow, and there is still no explain for updates... I may try to upgrade to mysql 5.6 from dotdeb, or try mariadb. :)
UPDATE 2: I tried different row formats (dynamic and compressed), but it was not (significantly) faster either.
UPDATE 3: I updated to MySQL 5.6.17 (from dotdeb), and the performance is generally the same, but I finally got an explain for the update:
EXPLAIN UPDATE job_queue.job_queue SET state=4 WHERE error_counter>=5 AND state=1;
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | job_queue | index | NULL | PRIMARY | 8 | NULL | 70222 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
This only confirms what could be suspected all along: the UPDATE is not using any indexes. So the question remains: WHY?
I will try to create a minimal example that produces this behaviour; maybe in the course of that, I will find some answers....
I've found the problem! The state
column was defined as char(1)
. Interestingly, SELECTs containing state=1
most of the times do use indexes, but UPDATEs never. However, specifying state="1"
always uses indexes.
So in summary, if col
is a char
and col
is indexed:
UPDATE table SET col=2 WHERE col=1
will not use an index and thus be slow, whereas
UPDATE table SET col=2 WHERE col="1"
will use an index and thus be fast.