Bug #107026 Update performs full table scan due to too many indexes

• Submitted: 14 Apr 2022 16:17
• Modified: 15 Apr 2022 7:00
• Reporter: huang rong
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.36
• OS: Debian
• CPU Architecture: x86
• Tags: full table scan, UPDATE

[14 Apr 2022 16:17] huang rong

Description:

What you did:
execute update table where primary key in many values(more than 16,000)
-------
UPDATE `table1` SET `clientid` = '11' WHERE id IN ('1', '2', '3', '4', '5',......)
-------

What you wanted to happen:
update run fast by primary key.

What actually happened:

mysql> select count(0) from table1;
+----------+
| count(0) |
+----------+
|  2000240 |
+----------+

update run fast in Server version: 5.6.39, explain plan as below:
+----+-------------+--------+-------+---------------+---------+---------+-------+-------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows  | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+-------+-------+-------------+
|  1 | SIMPLE      | table1 | range | PRIMARY       | PRIMARY | 8       | const | 16001 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+-------+-------+-------------+

Unexpected: update perform full table scan and run very slow in Server version: 5.7.36, explain plan as below:

+----+-------------+--------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | UPDATE      | table1 | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1994680 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

both the two OS runing mysql are in low presure.

conver the update sql into select and explain it in mysql 5.6.39 and 5.7.36, it query table by primary key and run fast.
in mysql 5.6.39
+----+-------------+--------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | table1 | range | PRIMARY       | PRIMARY | 8       | NULL | 16001 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+-------+-------------+
in mysql 5.7.3
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | table1 | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 16001 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+

How to repeat:
-- create test table
CREATE TABLE `table1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `clientid` mediumint(9) NOT NULL DEFAULT '0',
  `a1` mediumint(9) NOT NULL DEFAULT '0',
  `a2` mediumint(9) NOT NULL DEFAULT '0',
  `a3` mediumint(9) NOT NULL DEFAULT '0',
  `a4` mediumint(9) NOT NULL DEFAULT '0',
  `a5` mediumint(9) NOT NULL DEFAULT '0',
  `a6` mediumint(9) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `a1` (`a1`),
  KEY `a2` (`a2`),
  KEY `a3` (`a3`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

# insert 2000240 test items 
for i in {1..10}
do
  mysql stickyads -e "insert table1(clientid) value(1);"
done

for i in {1..10}
do
  mysql stickyads -e "insert table1(clientid) select clientid from table1 ;"
done

for i in {1..199}
do
  mysql stickyads -e "insert table1(clientid) select clientid from table1 limit 10000;"
done

# generate test sql
string1=""
string2=""
for i in {1..16000}
do
  let j=i*16
  string1=${string1}${j}","
  string2=${string2}"'"${j}"',"
done
string1=${string1}"0"
string2=${string2}"'0'"
# update multi In condition, no single quotes in id colum value
echo "explain UPDATE table1 SET clientid = 11 WHERE id IN ($string1)" > ~/multiIn.sql 
# update multi In condition, with single quotes in id colum value
echo "explain UPDATE table1 SET clientid = 11 WHERE id IN ($string2)" > ~/multiInQuotes.sql
# select multi In condition, no single quotes in id colum value
echo "explain select * from table1 WHERE id IN ($string1)" > ~/multiInSelect.sql 
# select multi In condition, with single quotes in id colum value
echo "explain select * from table1 WHERE id IN ($string2)" > ~/multiInQuotesSelect.sql

# explian sql 
mysql stickyads -e "source /root/multiIn.sql; source /root/multiInQuotes.sql;"
mysql stickyads -e "source /root/multiInSelect.sql; source /root/multiInQuotesSelect.sql;"

while there are 4 indexes, in mysql 5.6.39, update and select are good to query primary key; in mysql 5.7.3, select is good to query primary key, but update not

# drop 3 indexes
alter table table1 drop index a3;
alter table table1 drop index a2;
alter table table1 drop index a1;

# explian sql again
mysql stickyads -e "source /root/multiIn.sql; source /root/multiInQuotes.sql;"
mysql stickyads -e "source /root/multiInSelect.sql; source /root/multiInQuotesSelect.sql;"

while there is only primary key left, both in mysql 5.6.39 and 5.7.3, update and select are good to query primary key;

[14 Apr 2022 16:20] huang rong

Correct the impact MySQL version from 5.7.3 to 5.7.36. Sorry for the type issue.

[14 Apr 2022 16:26] huang rong

mysql config file

Attachment: my.cnf (application/octet-stream, text), 3.31 KiB.

[15 Apr 2022 7:00] huang rong

update run as expected in mysql 5.7.36 by SET GLOBAL range_optimizer_max_mem_size=0;

[15 Apr 2022 12:13] MySQL Verification Team

Thank you for your report. 

Also, we are glad that you have found all by yourself a configuration variable that can change the behaviour. In cases like the one that you described, execution speed depends a lot on the distribution of the values in the IN clause. 

For such a large number of values, Optimiser would have to spend lot's of time to calculate which path of execution would be faster. Optimise phase would run several times longer than the execution phase.

That is why DBA or application programmer is provided with a tool to help the server execute the query faster.

Closed by the reporter.