Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.1.36, 10.1.37
-
None
Description
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) |
While running the equivalent update is picking the correct index
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) |
The enum replace with CHAR(1) style lack usage of PRIMARY KEY on REPLACE or ON DUPLICATE KEY