| 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: | |
| 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 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.
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';