Salut gotcha5832.
Comme votre table des codes postaux est quasiment statique, vous avez raison de l'utiliser avec le moteur MyIsam pour la performance en lecture.
Envoyé par
Escartefigue
Ce que vous devez faire, c'est ajouter une colonne technique, qui servira d'identifiant primaire et donc unique.
Aucun intérêt de créer une colonne technique servant de clef primaire car cela ne va pas résoudre le problème de performance de gotcha5832.
D'ailleurs, vous ne vous servirez jamais de cette colonne. Donc aucune utilité en l'état !
La requête est la suivante :
1
2
3
4
| explain select *
from `test`
where CODEPAYS = 'fr'
and CP = '75014' |
Le problème que vous rencontrez est comment définir cette clef primaire à partir de la requête ci-dessus afin de rendre les accès le plus performant !
Le couple (codepays ; code postal) utilisé dans la clause "where" devrait faire l'affaire, sauf que vous aurez des doublons.
Il ne peut pas être utiliser en tant que clef primaire car la contrainte est justement d'avoir des valeurs uniques et non nulles.
La solution, avec le moteur MyIsam, consiste à ajouter une troisième colonne, genre identifiant auto incrémenté.
A l'inverse de la solution d'Escartefigue, cette identifiant sera incrémenté de +1, uniquement dans le cas des doublons du couple (codepays ; code postal).
Cette colonne n'a pas besoin d'avoir une grande amplitude, puisque pour un code postal, vous aurez au maximum une centaine de ville.
Ce qui donne la structure suivante :
1
2
3
4
5
6
7
8
9
10
11
| DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`
( `CODEPAYS` char(2) not null,
`CP` char(10) not null,
`id` smallint unsigned not null auto_increment,
`VILLE` varchar(255) not null,
primary key (`codepays`,`cp`,`id`)
) ENGINE=MyIsam
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED; |
Ajouter un index sur une ou plusieurs colonnes autre que les colonnes de la clef primaire ne seront pas prise en compte par l'optimiseur MySql, vis-à-vis de la requête.
Ce qui donne en faisant un explain :
1
2
3
4
5
6
7
8
9
10
11
| explain select *
from `test`
where CODEPAYS = 'fr'
and CP = '75014'
--------------
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | PRIMARY | PRIMARY | 12 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+ |
Il y a un problème dans la création de l'index (ou clef primaire) qui va justement contenir des chaînes de caractères.
Le choix du charset n'est pas anodin car si vous utilisez le français et l'anglais, autant mettre "latin1" qui va occuper 1 octet pour 1 caractère, à l'inverse de l'utf8 qui peut occuper jusqu'à 3 octets.
De plus, ces chaînes de caractères peuvent avoir comme longueur au maximum 255 caractères.
L'astuce est alors de réduire la longueur de cet index à sa partie utile en la fixant par un nombre.
Dans l'explain ci-dessus, la longueur de l'index est de 2 (pour le code pays) et 10 (pour la code postal), ce qui donne 12 (key_len).
Admettons que la partie utile soit de 5 caractères pour le code postal. Ce qui donne, par exemple :
primary key (`codepays`,`cp`(5),`id`)
Et voici l'explain, ainsi que le descriptif des index :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| explain select *
from `test`
where CODEPAYS = 'fr'
and CP = '75014'
--------------
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | PRIMARY | PRIMARY | 7 | const,const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
--------------
show index from test
--------------
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | PRIMARY | 1 | CODEPAYS | A | NULL | NULL | NULL | | BTREE | | |
| test | 0 | PRIMARY | 2 | CP | A | NULL | 5 | NULL | | BTREE | | |
| test | 0 | PRIMARY | 3 | id | A | 69 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
On remarque dans l'explain que la longueur de l'index était de 12 (2+10) et passe maintenant à 7 (2+5).
On retrouve ce 5 dans la colonne sub_part, à la ligne correspond à "CP".
Cette optimsation permet de reduire la largeur de cet index.
Par contre, là où Escartefigue à entièrement raison quand il dit :
Envoyé par
Escartefigue
.. et garanti la stabilité de votre base en cas d'évolution du contenu fonctionnel de vos 3 colonnes existantes
Comment garantir la stabilité fonctionnelle du contenu quand celle-ci évolue dans le temps ?
Si je prends l'exemple du fichier de la poste : https://www.data.gouv.fr/fr/datasets...codes-postaux/
celui-ci est remis à jour de temps en temps. Par exemple, la dernière version date du 26 décembre 2016.
Si un code postal vient à disparaitre, il faut trouver son équivalent dans la nouvelle version.
L'ajout d'une simple clef primaire de type technique ne résout en aucune façon ce problème.
D'autant que le problème peut devenir complexe quand un même code postal, dans le temps, peut désigner des communes différentes.
Il faudrait ajouter une date afin de faire la distinction dans l'évolution du code postal.
A vrai dire, je n'ai pas trop réfléchi sur ce genre de problème, ni comment gérer dans le temps les évolutions du code postal.
@+
Quel index unique?
Répondre avec citation
de mon aide, vous pouvez cliquer sur 
.
Partager