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)