Description:
The force index hint is ignored in update commands that don't fit into the range_optimizer_max_mem_size size. For select commands, it works independently of the size of the range_optimizer_max_mem_size.
How to repeat:
Create the table:
CREATE TABLE `test_table` (
`col1` varchar(128) NOT NULL DEFAULT '',
`col2` varchar(128) NOT NULL DEFAULT '',
`col3` varchar(99) NOT NULL DEFAULT '',
`col4` varchar(150) NOT NULL DEFAULT '',
`col5` int(10) unsigned NOT NULL DEFAULT '0',
`col6` int(10) unsigned NOT NULL DEFAULT '0',
`col7` tinyint(3) unsigned NOT NULL DEFAULT '1',
`col8` datetime DEFAULT NULL,
`col9` tinyint(3) unsigned NOT NULL DEFAULT '0',
`col10` datetime NOT NULL,
`col11` tinyint(3) unsigned NOT NULL DEFAULT '0',
`col12` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`col13` timestamp NOT NULL,
`col14` varchar(32) NOT NULL DEFAULT 'empty',
`col15` varchar(99) NOT NULL DEFAULT '_',
PRIMARY KEY (`col1`,`col14`,`col2`,`col3`,`col15`),
KEY `temp_del_time_idx` (`col10`),
KEY `idx_col2_col3` (`col2`,`col3`),
KEY `idx_col12` (`col12`),
KEY `col14_index` (`col14`),
KEY `pk_substitute` (`col14`,`col2`,`col3`,`col15`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Set a very low range_optimizer_max_mem_size:
MySQL > set session range_optimizer_max_mem_size=100;
Query OK, 0 rows affected (0.00 sec)
Run the select:
explain SELECT * FROM test_table FORCE KEY(pk_substitute) WHERE test_table.col14 = '22222222AAAAAAAAAAAAAAAAAA' AND (test_table.col2, test_table.col3, test_table.col15) IN ( ( "AAAA","1111111111","aaaa" ), ( "BBBB","2222222222","bbbb" ), ( "CCCC","3333333333","cccc" ), ( "AAAA","4444444444","dddd" ), ( "BBBB","5555555555","eeee" ), ( "CCCC","6666666666","ffff" ), ( "AAAA","7777777777","gggg" ), ( "BBBB","8888888888","hhhh" ), ( "CCCC","9999999999","iiii" ), ( "AAAA","11111111110","jjjj" ), ( "BBBB","12222222221","kkkk" ), ( "CCCC","13333333332","llll" ), ( "AAAA","14444444443","mmmm" ), ( "BBBB","15555555554","nnnn" ), ( "CCCC","16666666665","oooo" ), ( "AAAA","17777777776","ppp" ), ( "BBBB","18888888887","qqqq" ));
+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | pk_substitute | pk_substitute | 98 | const | 1 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
It's using the pk_substitute.
Run the update:
explain UPDATE test_table FORCE KEY(pk_substitute) SET test_table.col11 = 1, test_table.col9 = 1 WHERE test_table.col14 = '22222222AAAAAAAAAAAAAAAAAA' AND (test_table.col2, test_table.col3, test_table.col15) IN ( ( "AAAA","1111111111","aaaa" ), ( "BBBB","2222222222","bbbb" ), ( "CCCC","3333333333","cccc" ), ( "AAAA","4444444444","dddd" ), ( "BBBB","5555555555","eeee" ), ( "CCCC","6666666666","ffff" ), ( "AAAA","7777777777","gggg" ), ( "BBBB","8888888888","hhhh" ), ( "CCCC","9999999999","iiii" ), ( "AAAA","11111111110","jjjj" ), ( "BBBB","12222222221","kkkk" ), ( "CCCC","13333333332","llll" ), ( "AAAA","14444444443","mmmm" ), ( "BBBB","15555555554","nnnn" ), ( "CCCC","16666666665","oooo" ), ( "AAAA","17777777776","ppp" ), ( "BBBB","18888888887","qqqq" ));
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | UPDATE | test_table | NULL | index | NULL | PRIMARY | 1468 | NULL | 1 | 100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
Increase the size of range_optimizer_max_mem_size:
MySQL > set session range_optimizer_max_mem_size=40000;
Query OK, 0 rows affected (0.00 sec)
Then it starts to use the pk_substitute as well:
explain UPDATE test_table FORCE KEY(pk_substitute) SET test_table.col11 = 1, test_table.col9 = 1 WHERE test_table.col14 = '22222222AAAAAAAAAAAAAAAAAA' AND (test_table.col2, test_table.col3, test_table.col15) IN ( ( "AAAA","1111111111","aaaa" ), ( "BBBB","2222222222","bbbb" ), ( "CCCC","3333333333","cccc" ), ( "AAAA","4444444444","dddd" ), ( "BBBB","5555555555","eeee" ), ( "CCCC","6666666666","ffff" ), ( "AAAA","7777777777","gggg" ), ( "BBBB","8888888888","hhhh" ), ( "CCCC","9999999999","iiii" ), ( "AAAA","11111111110","jjjj" ), ( "BBBB","12222222221","kkkk" ), ( "CCCC","13333333332","llll" ), ( "AAAA","14444444443","mmmm" ), ( "BBBB","15555555554","nnnn" ),
( "CCCC","16666666665","oooo" ), ( "AAAA","17777777776","ppp" ), ( "BBBB","18888888887","qqqq" ));
+----+-------------+------------+------------+-------+---------------+---------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------------+---------+-------------------------+------+----------+-------------+
| 1 | UPDATE | test_table | NULL | range | pk_substitute | pk_substitute | 1082 | const,const,const,const | 17 | 100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------------+---------+-------------------------+------+----------+-------------
Suggested fix:
The optimizer should be able to use a more efficient plan than a costly full-table scan. It should use the Force index independently of the size of the range_optimizer_max_mem_size as the select commands.
