[#MDEV-17840] Full scan when using ON DUPLICATE KEY UPDATE or REPLACE

CREATE TABLE `stats_datas` (

  `id_stats` varchar(255) NOT NULL DEFAULT '0',

  `type_periode` enum('J','M','A') NOT NULL DEFAULT 'J',

  `date_debut` bigint(11) NOT NULL DEFAULT '0',

  `date_fin` bigint(11) NOT NULL DEFAULT '0',

  `nb_requetes` int(11) NOT NULL DEFAULT '0',

  `nb_courriels` int(11) NOT NULL DEFAULT '0',

  `nb_telephone` int(11) NOT NULL DEFAULT '0',

  `nb_fax` int(11) NOT NULL DEFAULT '0',

  `nb_poste` int(11) NOT NULL DEFAULT '0',

  `nb_rdv` int(11) NOT NULL DEFAULT '0',

  `temps_total` bigint(20) NOT NULL DEFAULT '0',

  `nb_courriels_echanges` int(11) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id_stats`,`type_periode`,`date_debut`),

  KEY `date_fin` (`date_fin`),

  KEY `nb_requetes` (`nb_requetes`),

  KEY `req_ag_1` (`id_stats`,`type_periode`,`date_debut`),

  KEY `req_ag_2` (`date_debut`),

  KEY `req_ag_3` (`id_stats`,`type_periode`,`date_debut`,`date_fin`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql tsce_unedic < 1.sql

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	INSERT	stats_datas	ALL	NULL	NULL	NULL	NULL	NULL	NULL

cat 1.sql

explain insert into stats_datas (id_stats,type_periode,date_debut,date_fin)  values ('0710000123456000000000000000','A','18010100000000000','18123199999999999') ON DUPLICATE KEY UPDATE id_stats= '0710000123456000000000000000', type_periode = 'A', date_debut= '18010100000000000', date_fin='18123199999999999';

+------+-------------+-------------+------+---------------+------+---------+------+------+-------+

| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra |

+------+-------------+-------------+------+---------------+------+---------+------+------+-------+

|    1 | INSERT      | stats_datas | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL  |

+------+-------------+-------------+------+---------------+------+---------+------+------+-------+

explain replace into stats_datas (id_stats,type_periode,date_debut,date_fin)  values ('0710000123456000000000000000','A','18010100000000000','18123199999999999') ;

+------+-------------+-------------+------+---------------+------+---------+------+------+-------+

| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra |

+------+-------------+-------------+------+---------------+------+---------+------+------+-------+

|    1 | INSERT      | stats_datas | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL  |

+------+-------------+-------------+------+---------------+------+---------+------+------+-------+

1 row in set (0.00 sec)

alter table stats_datas modify column `type_periode` char(1) NOT NULL DEFAULT 'J';

Query OK, 5342364 rows affected (44.06 sec)

Records: 5342364  Duplicates: 0  Warnings: 0

 explain replace into stats_datas (id_stats,type_periode,date_debut,date_fin)  values ('0710000123456000000000000000','A','18010100000000000','18123199999999999') ;

+------+-------------+-------------+------+---------------+------+---------+------+------+-------+

| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra |

+------+-------------+-------------+------+---------------+------+---------+------+------+-------+

|    1 | INSERT      | stats_datas | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL  |

+------+-------------+-------------+------+---------------+------+---------+------+------+-------+

1 row in set (0.00 sec)

explain update stats_datas set id_stats= '0710000123456000000000000000', type_periode = 'A', date_debut= '18010100000000000', date_fin='18123199999999999' WHERE id_stats= '0710000123456000000000000000' AND type_periode = 'A' AND  date_debut= '18010100000000000';

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

| id   | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra       |

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

|    1 | SIMPLE      | stats_datas | range | PRIMARY       | PRIMARY | 266     | NULL |    1 | Using where |

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

1 row in set (0.00 sec)

MariaDB [test]> insert into stats_datas (id_stats,type_periode,date_debut,date_fin) values ('0710000123456000000000000000','A','18010100000000000','18123199999999999');

Query OK, 1 row affected (0.02 sec)

MariaDB [test]> insert into stats_datas (id_stats,type_periode,date_debut,date_fin) select seq,'A',seq,seq from seq_1_to_1000;

Query OK, 1000 rows affected (0.03 sec)

Records: 1000  Duplicates: 0  Warnings: 0

MariaDB [test]> FLUSH TABLES; FLUSH STATUS;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into stats_datas (id_stats,type_periode,date_debut,date_fin) values ('0710000123456000000000000000','A','18010100000000000','18123199999999999') ON DUPLICATE KEY UPDATE id_stats= '0710000123456000000000000000', type_periode = 'A', date_debut= '18010100000000000', date_fin='18123199999999999';

Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> SHOW STATUS LIKE '%Handler_read%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| Handler_read_first       | 0     |

| Handler_read_key         | 1     |

| Handler_read_last        | 0     |

| Handler_read_next        | 0     |

| Handler_read_prev        | 0     |

| Handler_read_retry       | 0     |

| Handler_read_rnd         | 0     |

| Handler_read_rnd_deleted | 0     |

| Handler_read_rnd_next    | 0     |

+--------------------------+-------+

9 rows in set (0.00 sec)

MariaDB [test]> FLUSH TABLES; FLUSH STATUS;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> update stats_datas set id_stats= '0710000123456000000000000000', type_periode = 'A', date_debut= '18010100000000000', date_fin='18123199999999999' WHERE id_stats= '0710000123456000000000000000' AND type_periode = 'A' AND date_debut= '18010100000000000';

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

MariaDB [test]> SHOW STATUS LIKE '%Handler_read%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| Handler_read_first       | 0     |

| Handler_read_key         | 1     |

| Handler_read_last        | 0     |

| Handler_read_next        | 0     |

| Handler_read_prev        | 0     |

| Handler_read_retry       | 0     |

| Handler_read_rnd         | 0     |

| Handler_read_rnd_deleted | 0     |

| Handler_read_rnd_next    | 0     |

+--------------------------+-------+

9 rows in set (0.00 sec)

MariaDB [test]> FLUSH TABLES; FLUSH STATUS;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> replace into stats_datas (id_stats,type_periode,date_debut,date_fin) values ('0710000123456000000000000000','A','18010100000000000','18123199999999999') ;

Query OK, 1 row affected (0.00 sec)

MariaDB [test]> SHOW STATUS LIKE '%Handler_read%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| Handler_read_first       | 0     |

| Handler_read_key         | 1     |

| Handler_read_last        | 0     |

| Handler_read_next        | 0     |

| Handler_read_prev        | 0     |

| Handler_read_retry       | 0     |

| Handler_read_rnd         | 0     |

| Handler_read_rnd_deleted | 0     |

| Handler_read_rnd_next    | 0     |

+--------------------------+-------+

9 rows in set (0.00 sec)

[MDEV-17840] Full scan when using ON DUPLICATE KEY UPDATE or REPLACE Created: 2018-11-26 Updated: 2018-12-11 Resolved: 2018-12-10
Status:	Closed
Project:	MariaDB Server
Component/s:	N/A
Affects Version/s:	10.1.36, 10.1.37
Fix Version/s:	N/A

[MDEV-17840] Full scan when using ON DUPLICATE KEY UPDATE or REPLACE Created: 2018-11-26 Updated: 2018-12-11 Resolved: 2018-12-10