Bug #96328 INSERT ... ON DUPLICATE KEY UPDATE
Submitted: 25 Jul 2019 10:37 Modified: 25 Jul 2019 13:04
Reporter: HULONG CUI Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:all OS:Any
Assigned to: CPU Architecture:Any
Tags: Explain INSERT ... ON DUPLICATE KEY UPDATE type=all

[25 Jul 2019 10:37] HULONG CUI
Description:
when explain INSERT ... ON DUPLICATE KEY UPDATE
the result  type=ALL

> explain INSERT INTO mRowUpdate(id,`value`) VALUES(3, 'SuperMan') ON DUPLICATE 
          KEY UPDATE `id`=1;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | mRowUpdate | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

How do I check this  SQL going to index?

Website shows:
INSERT ... ON DUPLICATE KEY UPDATE Syntax:
If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs.

How to repeat:
CREATE TABLE `mRowUpdate` (
         `id` int(11) NOT NULL,
          `value` varchar(255) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB;

INSERT INTO `mRowUpdate` VALUES ('1', 'sss');
INSERT INTO `mRowUpdate` VALUES ('2', 'szh');
INSERT INTO `mRowUpdate` VALUES ('3', '9999');

explain INSERT INTO mRowUpdate(id,`value`) VALUES(3, 'SuperMan') ON DUPLICATE KEY UPDATE `value`='SuperMan';
[25 Jul 2019 13:04] MySQL Verification Team
Hello Mr. Jacky,

Thank you for your bug report.

However, this is not a bug. For several reasons. First one is that INSERT usually does not need to use any indices. There are exceptions, like INSERT from SELECT etc ......

More important, when a table has a small number of rows, like less then 100 (one hundred), indices are not used since the scanning is much faster in that case.

This is all fully explained in our Reference Manual.