1

I have these simple SELECT and UPDATE requests on MySQL using the primary key, there are about 13 million rows in the table.

When running an EXPLAIN on both, I notice that the UPDATE one does not use the primary key in the same way and is reported as slow in the slow query log:

  • type is "range" for UPDATE and const for SELECT
  • extra is "using where" for UPDATE and "NULL" for SELECT

Here is the data:

enter image description here

enter image description here

Thank you for your help

1

So I would have to look at the query optimizer source code of why it is printing range, but my guess is that for writes it will just use a limited number of write methods for simplification/limitation of EXPLAIN. If we look at the actual low row level operations of both queries, we can see that the SELECT:

MariaDB [db]> flush status; SELECT `BuyPacker_Email`.`emailOpen` = 1567007592 FROM `BuyPacker_Email` WHERE `BuyPacker_Email`.`emailId` = 17040352; SHOW STATUS like 'Hand%';
Query OK, 0 rows affected (0.000 sec)
1 row in set (0.000 sec)

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     | <----
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_retry         | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_delete         | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
27 rows in set (0.001 sec)

... does a single row read through and index. And for the update:

MariaDB [db]> FLUSH STATUS; UPDATE `BuyPacker_Email` SET `BuyPacker_Email`.`emailOpen` = 1567007592 WHERE `BuyPacker_Email`.`emailId` = 17040352; SHOW STATUS like 'Hand%';
Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)
Rows matched: 1  Changed: 0  Warnings: 0

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 2     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 2     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     | <-----
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_retry         | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_delete         | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 1     |  <----
| Handler_write              | 0     |
+----------------------------+-------+
27 rows in set (0.000 sec)

... it uses the exact same read strategy + the required write to update the record.

So there is no overhead in terms of reading more records than necessary or any difference in terms of more expensive read plans. Whether it is a simplification for EXPLAIN, or a limitation due to write queries, where it cannot go too far as to tell; or it shows a real difference because how writes work, and marks a real performance impact, one would need to go into implementation details- but for most cases, as long as the rows column says "1", I don't see any problems with the query- a range of 1 row should be almost equivalent to a const access, broadly speaking.

  • thank you for this very detailed answer, I'll look into this and maybe open a bug for the MySQL team – syl.fabre Aug 30 at 14:37
  • 1
    How would you report this as a bug? You needed one row acted upon (UPDATE) or delivered (SELECT) and that is what happened when the query was completed. – Wilson Hauck Sep 2 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.