Discussion :

[frederic.bouchery]

Bonjour,

Je me tire les cheveux avec une requête extrêmement longue, et je n'arrive pas à comprendre pourquoi.

J'ai une table "PersonneMorale" qui contient le code Insee de la localité où réside cette personne.
J'ai une deuxième table où le code Insee me donne ville, région, département, etc.

Le champ "codeInsee" est de type varchar(5) dans les deux tables.
Sur la table PersonneMorale, j'ai placé un index sur ce champ et dans la table "localite", le codeInsee est la clef primaire.

Voici une explain que je ne comprend pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
mysql> explain SELECT nom FROM PersonneMorale p LEFT JOIN localite l ON p.codeInsee = l.codeInsee;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | p     | ALL   | NULL          | NULL    | NULL    | NULL | 22682 |             |
|  1 | SIMPLE      | l     | index | NULL          | PRIMARY | 7       | NULL | 36746 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
2 rows in set (0.00 sec)
 
mysql> explain SELECT nom, l.ville FROM PersonneMorale p LEFT JOIN localite l ON p.codeInsee = l.codeInsee;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | p     | ALL  | NULL          | NULL | NULL    | NULL | 22682 |       |
|  1 | SIMPLE      | l     | ALL  | NULL          | NULL | NULL    | NULL | 36746 |       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+

Pourquoi le fait d'ajouter un champ dans la sélection fait que ma requête ne prend plus l'index primaire ?
D'ailleurs, pourquoi ma clef primaire n'est pas dans les clefs possibles ?
Comment je peux faire pour que ma requête ne soit pas aussi longue (plus de 2 minutes !!!!) ?

Mes tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE `PersonneMorale` (
  `idPersonneMorale` int(11) NOT NULL AUTO_INCREMENT,
  [...snipp...]
  `nom` varchar(255) NOT NULL,
  `cp` varchar(5) NOT NULL,
  `codeInsee` varchar(5) NOT NULL,
  [...snipp...]
  PRIMARY KEY (`idPersonneMorale`),
  KEY `idxCodeInsee` (`codeInsee`)
) ENGINE=InnoDB
 
CREATE TABLE `localite` (
  `codeInsee` varchar(5) NOT NULL,
  `ville` char(50) NOT NULL,
  `arrondissement` char(30) NOT NULL,
  `departement` char(30) NOT NULL,
  `region` char(30) NOT NULL,
  `emploi` char(40) DEFAULT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  PRIMARY KEY (`codeInsee`)
) ENGINE=InnoDB

Merci pour vos lumières

Frédéric

[Maljuna Kris]

Saluton, Fred,

comment va ton furet ?

Pour ton problème je te conseille de prendre une clef numérique autoincrement pour faire tes jointures et de l'utiliser comme foreign key dans la table PersonneMorale, plutôt que cette colonne de type varchar(5).
Je sais, ce n'est pas en forme normale puisque la colonne codeInsee serait tout à fait candidate, dans l'absolu, à jouer ce rôle de clé primaire, mais....
Déclare cette colonne codeInsee UNIQUE dans la table localité et utilise une clé autoincrmentée.

Sinon, toujours à Rennes ? Moi je suis en retraite depuis mercredi soir.

Amike.

[Grandal]

Bonjour,


Je ne sais pas si cela va t'aider mais sur MariaDb 5.3 (le Mysql de l'équipe Monty), ta requête utilise bien les index.


Bon weekend

Francis

[frederic.bouchery]

C'est bien ce qui m'inquiete, car je ne vois pas de raison pour que l'indexe ne soit pas utilisé.
Pour la petite histoire, ma table PersonneMorale contenait ce champs "codeInsee", mais sans index. Je l'ai ajouté via phpmyadmin (ce que je n'aime pas trop d'habitude) et la requête ALTER a tournée pendant de longues minutes. J'ai stoppé la requête ("stop" du navigateur) et j'ai regardé en ligne de commande (je préfère) si l'index était là, ce qui était le cas.

Voyant que mes select ne se comportaient pas normalement, je pensais à des indexes foirés, alors j'ai fait un dump SQL, j'ai droppé et recréé la base, puis j'ai restitué le dump : même résultat.

Ensuite, j'ai constaté que ma table de localités (c'est moi qui l'ai créé) avaient un codeInsee de type "char(6)" (ben oui, il faut penser à nos amis des DOM), je l'ai passé en "varchar(5)" pensant qu'une différence de type pourrait être à la source du PB, mais non.

Personnellement, je suis contre les auto-incréments, surtout que le code Insee fait parfaitement l'affaire, même s'il n'est pas numérique. Mes benchs sur la différence entre numérique et chaîne ne m'ayant pas démontrés une grosse différence.

Here it is : WTF ?

[frederic.bouchery]

comment va ton furet ?

Il est mort !

Sinon, toujours à Rennes ? Moi je suis en retraite depuis mercredi soir.

Yep, chez Ouestfrance-multimédia.
Bon, moi je ne suis pas à la retraite, mais ça serait intéressant que je me relance dans la communauté PHP de Rennes 12C4.

Bonne retraite Perso, il n'y a pas d'urgence, je m'éclate dans mon travail, donc ....

[ericd69]

salut,

je pense comme Maljuna Kris...

n'oublie pas comment est implémenté un index texte... il se peut que mysql ait du mal avec les index de clé primaire non numériques...

en plus, les algorithmes de comparaison texte sont poussifs par rapport à la comparaison de nombres...

je pense que tu serais gagnant de mettre un int auto_increment comme clé primaire de `localite` et de remplacer la colonne `codeInsee` par une colonne `idVille` et là, ça devrait utiliser l'index de clé primaire de `localite` pour ta jointure...

[CinePhil]

Il est effectivement curieux que les index ne soient pas utilisés mais le modèle de données est quand même à revoir !

personneMorale -1,1----résider----0,n- ville -1,1----situer----1,n- departement -1,1----situer----1,n- region

Je suis par ailleurs dubitatif sur le caractère NOT NULL des arrondissements !
Il y a très peu de villes qui ont des arrondissements. Je modéliserais donc de cette façon :
ville -0,n----avoir----(1,1)- arrondissement -0,n----résider----0,1- ville

Et du coup je transformerais la première association résider de cette façon :
personneMorale -0,1----résider----0,n- ville
Ainsi pas de redondance entre l'arrondissement et la ville d'une personne morale.

Au fait, si dans votre modèle il y a aussi des personnes physiques, faites un héritage :
personneMorale -(1,1)----être----0,1- personne
personnePhysique -(1,1)----être----0,1-----|

Et les association "résider" que j'ai données ci-dessus s'appliquent cette fois ci à personne et non plus à personne morale.

Faites ensuite un modèle organisationnel de données à l'aide de vues pour reconstituer vos données complètes sur les différents types de personnes. Ce sont ces vues que votre application interrogera.

Vous aurez ainsi un modèle normalisé qui devrait être plus performant.

Et au passage, je suis également un fervent partisan des clés auto-incrémentées pour toutes les tables issues des entités types fortes du MCD.

region (rgn_id, rgn_code_insee, rgn_nom)
departement (dpt_id, dpt_id_region, dpt_code_insee, dpt_nom)
ville (vil_id, vil_id_departement, vil_code_insee, vil_nom)
arrondissement (arr_id_ville, arr_numero)
personne (prs_id, prs_nom, [colonnes communes à tousles types de personnes])
personneMorale (prm_id_personne, prm_siret, [autres colonnes spécifiques aux personnes morales])
personnePhysique (prp_id_personne, prp_prenom, [autres colonnes spécifiques aux personnes physiques])
prs_resider_vil (prv_id_personne, prv_id_ville)
prs_resider_arr (pra_id_personne, pra_id_arrondissement)

[frederic.bouchery]

Bonjour à tous,

En ce qui concerne l'identifiant "codeInsee", je ne peux pas le passer en numérique auto-incrémenté, car c'est un code normalisée par l'INSEE. Le deux premier caractère contiennent le département, et les 3 suivants un chiffre correspondant à l'ordre plus ou moins alphabétique des villes dans le département moins celles qui ont disparues.
Dans le cas de la Corse, le numéro de département est .... 2A ou 2B !!

Ensuite, pour la remarque sur l'arrondissement, pour information, les arrondissements parisien, lyonnais et marseillais sont des arrondissements de communes. Un arrondissement (tout court) est une sous entité de département lui même regroupant plusieurs villes. Par exemple, en Ille-et-Vilaine, il y a 4 arrondissements : Rennes, Reudon, Vitré et Fougères.

Maintenant, pour comprendre ma démarche, il faut savoir que je ne cherche pas à normaliser, mais au contraire, à dé-normaliser mes données pour une indexation dans le moteur de recherche Solr. Ma table PersonneMorale contient déjà l'adresse de celle-ci (il ne peut y en avoir qu'une) ainsi qu'un codeInsee de la ville. Pour permettre un "facettage" de me personnes morales, sur la localité, et afin de ne pas avoir une liste de 1.000 villes, j'ai récupéré la base Insee des communes de France qui me permet de faire un découpage "région/département/arrondissement/ville" (les arrondissements de commune ou les cantons ne m'intéresse pas).

La liste de mes personnes morales provient d'une base externe (une liste d'établissement français) qui m'ait renvoyé régulièrement pour mise à jour. Je n'ai donc pas la main sur la création de l'identifiant de ville (codeInsee).

Donc, maintenant, si on fait abstraction du modèle de données, je vais continuer à comprendre pourquoi mes indexes ne sont pas exploités.

Bonne journée

Frédéric

[CinePhil]

En ce qui concerne l'identifiant "codeInsee", je ne peux pas le passer en numérique auto-incrémenté, car c'est un code normalisée par l'INSEE. Le deux premier caractère contiennent le département, et les 3 suivants un chiffre correspondant à l'ordre plus ou moins alphabétique des villes dans le département moins celles qui ont disparues.
Dans le cas de la Corse, le numéro de département est .... 2A ou 2B !!

Effectivement, le code INSEE, comme tout code, fut-il entièrement numérique, doit être dans une colonne de type CHAR ou VARCHAR. On ne fait pas de calculs avec un code !

Ensuite, pour la remarque sur l'arrondissement, pour information, les arrondissements parisien, lyonnais et marseillais sont des arrondissements de communes. Un arrondissement (tout court) est une sous entité de département lui même regroupant plusieurs villes. Par exemple, en Ille-et-Vilaine, il y a 4 arrondissements : Rennes, Reudon, Vitré et Fougères.

Là tu m'apprends un truc, merci !

Maintenant, pour comprendre ma démarche, il faut savoir que je ne cherche pas à normaliser, mais au contraire, à dé-normaliser mes données

Ça c'est pas bien !

[frederic.bouchery]

Ça c'est pas bien !

Mes données sont normalisées, mais j'ai des tables supplémentaires présentes uniquement pour des raisons de performances ou d'indexation et qui dé-normalisent mon modèle.

Ces tables sont complètements "jetables", et peuvent à tout moment être regénérées à partir du modèle normalisé.

Sur certains projets, j'ai même déplacé les données dénormalisées dans une autre base qui étaient ensuite répliquée par une liaison maître-esclave. La base esclave étant en lecture seule directement sur un serveur LAMP load-balancé et stateless. Toutes les écritures étant renvoyées vers le serveur maître avec des processus de normalisation/dénormalisation asynchrone.

Donc, pas d’inquiétude, je ne suis pas un novice en "normalisation" de BD

[frederic.bouchery]

Bon, j'ai repris un dump "officiel" de ma base, j'ai modifié mon champ "codeInsee" dans mes deux tables pour le passer à "char(6)" puis j'ai ajouté l'index dans la table PersonneMorale.

Tout est rentré dans l'ordre, l'index est bien utilisé.

Précision supplémentaire, comme tous les vendredi, j'ai arrêté ma machine virtuelle et j'ai redémarré ce matin sans refaire de tests, puis j'ai migré. Pourtant Vendredi, j'avais redémarré mysql, donc je ne pense pas que cela soit trop lié.