Quel index unique?

Version imprimable

Quel index unique?

Bonjour à tous.

Je crée un script ajax d'autocompletion basé sur le code postal.
L'objectif étant que l'utilisateur entre son CP et que le système lui retourne une liste de commune et ceci sur 3 pays.

J'ai donc une table très simple
codepays | code postale | ville
Code:

1 2 3 4 5 6 7 8 CREATE TABLE IF NOT EXISTS `cp_autocomplete` ( `CODEPAYS` char(2) NOT NULL, `CP` varchar(10) NOT NULL, `VILLE` varchar(180) NOT NULL, KEY `CODEPAYS` (`CODEPAYS`), KEY `CP` (`CP`), KEY `VILLE` (`VILLE`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
la requête la plus sollicité sera

Code:

SELECT CP CodePostal, VILLE Ville FROM `cp_autocomplete` WHERE CP LIKE 750% AND CODEPAYS =" . $country;

J'aimerai réussir à avoir une table performante.

Sachant que cette table ne sera utilisé qu'en lecture j'ai crée la table en Myiam

Mais je ne vois pas quel index choisir, car
Les code postaux ne sont pas unique puisque plusieurs communes peuvent avoir un même code postale
Les villes ne sont pas unique puisque une ville peux avoir plusieurs code postale

Du coup j'ai pensé à un index sur les trois champ, mais il y a t il un interet?

Sinon quand je fait un explain sur une requete j'ai
possible_keys -->CODEPAYS,CP
key -->CP
Extra --> Using index condition; Using where

Dois je simplement metrre un index sur le couple CODEPAYS,CP ?

17/02/2017, 09h21
escartefigue

Bonjour,

Ce que vous devez faire, c'est ajouter une colonne technique, qui servira d'identifiant primaire et donc unique.

Un identifiant primaire ne doit JAMAIS être fonctionnel, et ce pour des raisons de stabilité, or, un code postal, une ville et un code pays peuvent changer : certaines communes fusionnent, certains pays sont redécoupés (ex : l'ex Yougoslavie éclatée en Croatie+Serbie+Montenegro+etc...) d'autres pays changent de nom et de code (ex Haute Volta devenue Burkina Faso)
Un identifiant primaire doit être concis, pour des raisons de performances.
Il est préférable d'éviter les formats Char, et encore plus Varchar pour les identifiants primaires

Donc, ajoutez une colonne de type integer en auto-incrément, et vous aurez résolu votre problème, optimisé vos perfs, et garanti la stabilité de votre base en cas d'évolution du contenu fonctionnel de vos 3 colonnes existantes ;)

Merci

Donc

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `cp_autocomplete` (
  `id` int(10) NOT NULL,
  `CODEPAYS` enum('BE','CH','FR') NOT NULL,
  `CP` varchar(40) NOT NULL,
  `VILLE` varchar(45) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
ALTER TABLE `cp_autocomplete`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`),
  ADD KEY `CODEPAYS` (`CODEPAYS`),
  ADD KEY `CP` (`CP`),
  ADD KEY `VILLE` (`VILLE`);

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.

Citation:

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 :
Code:

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 :
Code:

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 :
Code:

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 :

Code:

primary key (`codepays`,`cp`(5),`id`)

Et voici l'explain, ainsi que le descriptif des index :
Code:

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 :

Citation:

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.

@+

et sur les requete like?

Merci énomement pour vos explications, ceci me pernettant de résoudre mon problème mais surtout apprendre pour le futur.

Citation:

Envoyé par Artemus24

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.

Question probablement naive mais cet index "id" aura t il un impact sur les requetes de type
Code:

1 2 3 EXPLAIN SELECT * FROM `cp_autocomplete_test` WHERE `CODEPAYS` = 'fr' AND `CP` LIKE '513%' ORDER BY `id` DESC 1 SIMPLE cp_autocomplete_test range PRIMARY PRIMARY 7 131 Using index condition; Using where; Using filesort
Sachant que cet requete sera celle utlisé' puisque le but étant de proposer à la voler les villes dès les premier chiffres du code postal saisi.

Sinon pourquoi avoir ajouter la colonnes id en troisième position? cela a t il un impact?

18/02/2017, 15h37
Artemus24

Salut gotcha5832.

Citation:

Envoyé par gotcha5832

pourquoi avoir ajouter la colonnes id en troisième position?

La position n'a aucun importance dans le ligne, ni dans l'index.
Comme je vous l'ai dit précédemment, dans votre requête, vous sélectionnez suivant le critère de la clause where.
Or dans cette clause vous utilisez deux colonnes : (codepays ; code postal).
Afin d'accélérer l'accès à votre sélection, vous devez créer un index sur ces deux colonnes : (codepays ; code postal).
Or il se trouve que (codepays ; code postal) admet des doublons, donc le couple ne peut pas être candidate comme clef primaire.
Pour résoudre ce problème, vous devez ajouter une troisième colonne qui va rendre le couple (codepays ; code postal) unique.
Un simple incrément suffit à faire de ce triplé (codepays ; code postal ; id) un identifiant unique, donc une clef primaire.

Le triplé (codepays ; code postal ; id) est votre clef primaire car ces trois valeurs déterminent d'une manière unique la ligne dans votre table.

Citation:

Envoyé par gotcha5832

cet index "id" aura t'il un impact sur les requêtes de type ...

Dans votre requête, et je ne sais pas pourquoi, vous faites :

Code:

ORDER BY `id` DESC

cela ne vous sert à rien du tout, sinon à alourdir inutilement votre requête.
Si vous désirez obtenir la liste des villes dans l'ordre alphabétique, du plus petit au plus grand, autant les rentrer dans cet ordre dans votre table.

Donc pour répondre à votre question, c'est non, à la condition de ne pas référencer cette colonne dite technique dans vos requêtes.

Je détaille la colonne "extra" de votre explain : "Using index condition; Using where; Using filesort".
Le "using where" provient de la clause "where" dans votre requête. C'est nécessaire !

Le "Using filesort" provient de l'ajout de "desc" dans le "order by `id`".
Comme vous demandez un tri qui n'est pas celui naturel de votre table, vous demandez un travail supplémentaire qui n'a pas lieu d'être.
Comme je l'ai dit ci-dessus, si vous désirez avoir l'ordre alphabétique des villes pour le couple (codepays ; code postal) autant le trier au moment de l'insertion dans votre table.

Le "Using index condition;" provient de l'ajout d'une condition supplémentaire sur l'une des colonnes de votre clef primaire, autre que "where id = {valeur}".
C'est le cas en faisant une condition sur un sous-ensemble extraite de votre requête qui va occasionner un travail supplémentaire.
Par exemple "order by `ville`", si vous avez trois villes pour le couple (codepays ; code postal).

Je vous conseille de ne pas mettre de "order by `id`" qui ne fait qu'alourdir votre requête sans rien apporter de plus.

Est-ce que vous avez compris l'intérêt de ne prendre dans une chaîne de caractères que la partie utile ?
Comme le code postal en France est sur cinq chiffres (en corse cela se code "20" et non "2A" ou "2B" comme pour les départements), définir cette colonne sur dix caractères n'a aucun sens.
Mais comme vous ajoutez aussi des codes postaux étrangers, dont je ne connais pas la définition, ni la partie utile, il sera peut être nécessaire de supprimer cette restriction dans la création de l'index (clef primaire).

Savez-vous comment vous allez gérer les codes postaux dans le temps ?

@+
18/02/2017, 17h21
gotcha5832
Merci

Code:

ORDER BY `id` DESC

l'order à été ajouter par phpmyadmin mais n'est pas prévu dans mon code
Code:

1 2 3 EXPLAIN SELECT * FROM `cp_autocomplete_test` WHERE `CODEPAYS` LIKE 'FR' AND `CP` LIKE '513%' 1 SIMPLE cp_autocomplete_test range PRIMARY PRIMARY 7 131 Using index condition; Using where
Citation:

Est-ce que vous avez compris l'intérêt de ne prendre dans une chaîne de caractères que la partie utile ?

Je crois juste qu'afin d'avoir un table performante il est toujours préférable d'avoir la chaine la plus ajustée possible.
fr : 99999
be : 9999
ch : 9999
Donc char(5) est l'idéal

Code:

Savez-vous comment vous allez gérer les codes postaux dans le temps ?

Sachant que le but sera simplement de suggérer à l'utilisateur, et non limité ou contrôler l'adresse
Soit
- Ne rien faire
- une fois par an vider la table et la recréer avec les codes postaux mis à jour
18/02/2017, 18h26
Artemus24
Salut Gotcha5832.
Citation:
Envoyé par Gotcha5832

Citation:

Envoyé par Artemus24

Savez-vous comment vous allez gérer les codes postaux dans le temps ?

Sachant que le but sera simplement de suggérer à l'utilisateur, et non limité ou contrôler l'adresse
Soit

Ne rien faire
une fois par an vider la table et la recréer avec les codes postaux mis à jour
Cela dépend de l'usage que vous faites de votre table.

Si c'est pour rechercher le code postal d'une ville au moment où vous allez écrire un courrier, en effet, archiver les anciens codes postaux ne vous sert à rien.
A chaque révision du fichier des codes postaux par la poste, remplacer votre table par leur fichier est en effet la solution.

Oui, mais si vous stockez dans des tables, les adresses avec le code postal, et si celui-ci n'est pas revisité de temps en temps, vous pouvez vous retrouver avec une valeur qui peut très bien ne plus exister dans les fichiers de la poste.

@+