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:
Here is the data:
Thank you for your help
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.
