Behaviour diff b/w SELECT and UPDATE for range_optimizer_max_mem_size in MySQL

MySQL & MariaDB
1

I have a table called example_table with 7 million rows, and the range_optimizer_max_mem_size is set to 2MB. I understand that if a query’s range memory exceeds this limit, the optimizer switches to a full table scan. However, I noticed something interesting: when I run an UPDATE query with 4,000 IDs, it exceeds the limit and defaults to a full table scan. But when I convert that UPDATE into a SELECT query using the same 4,000 IDs, it does not exceed the range_optimizer_max_mem_size.

Can someone help me understand the difference in behavior between the SELECT and UPDATE queries?

Attached the table and test details below,

MySQL Version : 5.7.18

mysql> show create table example_table;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                     |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| example_table | CREATE TABLE `example_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=7000001 DEFAULT CHARSET=latin1 |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show indexes from example_table;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| example_table |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| example_table |          1 | name     |            1 | name        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| example_table |          1 | email    |            1 | email       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)


mysql> select count(*) from example_table;
+----------+
| count(*) |
+----------+
|  7000000 |
+----------+
1 row in set (1.22 sec)


mysql> explain UPDATE example_table SET age=100 where (id IN (<4k ids>) and ((id >= 1) and (id <= 7000000)));
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | UPDATE      | example_table | NULL       | index | NULL          | PRIMARY | 8       | NULL | 7000000 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.10 sec)



mysql> explain Select * from example_table where (id IN (<4k ids>) and ((id >= 1) and (id <= 7000000)));
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | example_table | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 4000 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2

In general, this is not a good practice. I would create a temporary table, load these ids into this table then JOIN with example_table. This allows MySQL to optimize the lookups of the ids with a different algorithm.

Run EXPLAIN FORMAT=JSON, and see if you get additional information about the differences.

If you don’t need to store more than 4 billion rows, you’re wasting 4 bytes per row by using a column type larger than you need. bigint uses 8 bytes to store the value 1 and 2^64.

Not only is 5.7 dead, this is a very old version of 5.7. MySQL 8 provides more insights into query performance using EXPLAIN ANALYZE. I would upgrade first before trying to diagnose any optimizer issues because, assuming a bug was found, it won’t be fixed.