Problème utilisation des index

**CCPMurat** · 22/06/2011, 17h29

Bonjour,

J'ai crée une base dont voici le code :

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
23
24
CREATE TABLE `base` (
  `Id` int(6) NOT NULL AUTO_INCREMENT,
  `Organisme` varchar(20) DEFAULT ' ' ',
  `Civilite` varchar(15) DEFAULT ' ' ,
  `Prenom` varchar(20) DEFAULT ' ',
  `Nom` varchar(31) DEFAULT ' ' COMMENT 'Nom de la personne',
  `Fonction1` varchar(30) DEFAULT ' ' COMMENT 'Fonction occupÃ©e',
  `Fonction2` varchar(30) DEFAULT ' ' COMMENT 'Fonction occupÃ©e',
  `Fonction3` varchar(30) DEFAULT ' ' COMMENT 'Fonction occupÃ©e',
  `Fonction4` varchar(30) DEFAULT ' ' COMMENT 'Fonction occupÃ©e',
  `Fonction5` varchar(30) DEFAULT ' ' COMMENT 'Fonction occupÃ©e',
  `Adresse` varchar(45) DEFAULT ' ' ,
  `CP` int(5) DEFAULT NULL,
  `Ville` varchar(35) DEFAULT ' ',
  `Telephone` varchar(15) DEFAULT ' ' ,
  `Portable` varchar(15) DEFAULT ' ' ,
  `E-Mail` varchar(50) DEFAULT ' ',
  `SiteInternet` varchar(45) DEFAULT ' ',
  `Territoire` varchar(13) DEFAULT ' ',
  PRIMARY KEY (`Id`),
  KEY `Territoire` (`Territoire`),
  KEY `Info` (`Organisme`,`Civilite`,`Prenom`,`Nom`,`Adresse`,`CP`,`Ville`,`Telephone`,`Portable`,`E-Mail`,`SiteInternet`),
  KEY `Fonction` (`Fonction1`,`Fonction2`,`Fonction3`,`Fonction4`,`Fonction5`)
) ENGINE=MyISAM  DEFAULT CHARSET=ascii AUTO_INCREMENT=331 ;

Voici quelque lignes insérées :

Code :

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

1
2
3
4
5
6
INSERT INTO `base` (`Id`, `Organisme`, `Civilite`, `Prenom`, `Nom`, `Fonction1`, `Fonction2`, `Fonction3`, `Fonction4`, `Fonction5`, `Adresse`, `CP`, `Ville`, `Telephone`, `Portable`, `E-Mail`, `SiteInternet`, `Territoire`) VALUES
(1, NULL, 'Monsieur', 'toto', 'toto', 'Agriculteurs', NULL, NULL, NULL, NULL, 'Chamb', 15000, 'LAVEIS', NULL, NULL, NULL, NULL, 'pm'),
(8, NULL, 'Monsieur', 'tata', 'tata', 'Conseiller municipal', NULL, 'Agriculteurs', NULL, NULL, 'Mons', 15000, 'VIRA', NULL, NULL, NULL, NULL, 'pm'),
(17, NULL, 'Monsieur', 'tutu', 'tutu', 'Conseiller municipal', NULL, NULL, 'Agriculteurs', NULL, 'Moled', 15300, 'LAVEISSEN', '05 99 73 99 89', NULL, NULL, NULL, 'pm'),
(24, NULL, 'Madame', 'titi', 'titi', 'Conseillere municipale', NULL, NULL, NULL, 'Agriculteurs', 'Secour', 15000, 'CEL', '05 99 73 99 89', NULL, NULL, NULL, 'pm'),
(154, NULL, 'Monsieur', 'tyty', 'tyty', 'Maire de Laviger', 'Agriculteurs', NULL, NULL, NULL, 'Buge', 15300, 'LAVIGER', '05 99 73 99 89', NULL, NULL, NULL, 'pm');

La ligne en gras indique que j'ai crée un index sur 5 colonnes (cf. 1er bloc de code; aperçu image jointe n°2)
D'après le cours sur le site ce n'est pas recommandé.
Admettons, je veux rechercher tous les agriculteurs, je suis obligé de comparer les valeurs des 5 colonnes à "Agriculteurs" car l'ordre de saisie peut changer.

Code :

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

1
2
3
SELECT *  
FROM `base` 
WHERE `Fonction1` LIKE 'a%' or `Fonction2` LIKE 'a%' or `Fonction3` LIKE 'a%' or `Fonction4` LIKE 'a%' or `Fonction5` LIKE 'a%'

Je voudrais savoir si en créant un (ou plusieurs) index je peux simplifier cette requête en faisant moins de 5 comparaisons.
Par exemple :

Code :

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

1
2
3
SELECT *  
FROM `base` 
WHERE `Fonction` LIKE 'a%'

/*Fonction fait référence à l'index crée sur les 5 colonnes fonctions*/
Remarque : Je sais que ma base ne respecte pas les normes, c'est volontaire donc je ne change pas la structure.
Remarque 2 : Un élus peut avoir plusieurs fonctions (ex : tata est conseiller municipal et agriculteurs)
La première image est un aperçu du résultat des 2 premiers bloc de code.
La seconde un aperçu des colonnes indéxées.
Le SGBD que j'utilise est MySQL.
J'espère avoir été assez clair sinon demander des informations supplémentaires.

Max

**dehorter olivier** · 22/06/2011, 17h44

Bonjour

la fonction des index n'est pas de "simplifier" les requêtes mais de les accélérer

En faisant un index sur plusieurs colonnes, cela ne crée pas une colonne synthétique

Je note ta remarque 1

par contre, ne connaissant pas MYSQL, je ne saurais dire si les index fonctionnent avec cette écriture

WHERE `Fonction` LIKE 'a%'

????

**punkoff** · 22/06/2011, 21h10

Remarque : Je sais que ma base ne respecte pas les normes, c'est volontaire donc je ne change pas la structure.

Et pourtant .. plus votre table va grossir et plus les performances des requêtes sur cette table vont devenir catastrophique.

La seule manière de "simplifier" votre requête actuelle est de passer par une re-modélisation de votre projet.

=> dégager les fonctions de cette table, faites une table de fonction et une table d'association.

Ensuite faites des jointures entre ces 3 tables et faites un jolie "fonction like 'a%'"

**CCPMurat** · 23/06/2011, 14h56

Créer une table "Elus" ET une table "FonctionO", jusque la pas de problèmes.
(cf image ci dessous)
Par contre pour la table d'association je ne vois pas ce qu'il faut mettre.

Remarque : La table FonctionO possède une seule colonne, je sais que ça ne correspond pas aux normes, c'est pour l'exemple.

**punkoff** · 23/06/2011, 15h05

D'après votre exemple une personne peut avoir jusqu'à 5 fonctions différentes.

donc il vous faut bien une relation entre cette table de fonctions et votre table initiale

MCD :
personne 0,n---- Possède ----- 0,n Fonction

Lors du passage au MPD la relation Possède va se transformer en table.
Vous pouvez y rajouter par exemple une colonne de classement si c'est utile pour vous.

t_personne_prs (prs_id, ....)
R_prs_fun_pfu (prs_id, fun_id, ...)
t_fonction_fun (fun_id, fun_desc, ....)

Du coup, votre requête devient :

Code :

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

1
2
3
4
5
6
 
select *
from t_personne_prs
inner join R_prs_fun_pfu on pfu.prs_id = prs.prs_id
inner join t_fonction_fun fun on fun.fun_id = pfu.fun_id
where fun.fun_desc like 'a%'

Le côté bénéfique :
votre LIKE se fera sur une table avec très peu de donnée (T_FONCTION_FUN) et vous évitera un scannage de table de T_PERSONNE_PRS comme vous l'avez surement actuellement.

Ensuite les jointures se font uniquement sur des pk => très rapide

Bref si vous pensez changer d'avis je vous conseillerais d'aller faire un tour sur le forum conception afin de retravailler proprement tout ceci

**CCPMurat** · 24/06/2011, 13h58

t_personne_prs (prs_id, ....) = elus(Id_e,...)
R_prs_fun_pfu (prs_id, fun_id, ...) = assoce(A_id_elus#,A_id_fonctiono#)
t_fonction_fun (fun_id, fun_desc, ....) = lsf(Numero,Fonction)
Donc ma requête doit être :

Code :

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

1
2
3
4
5
SELECT *, lsf.Fonction 
FROM elus, lsf 
INNER JOIN assoce ON assoce.A_id_elus = elus.Id_e
INNER JOIN lsf fon ON numero = assoce.A_id_Fonctiono
WHERE lsf.Fonction LIKE 'a%'

Or j'ai une erreur

#1054 - Unknown column 'elus.Id_e' in 'on clause'

Pourquoi ?

Je suis sur du nom de la table et du nom de l'attribut. Id_e est lié à A_id_elus, ils sont de même type et de taille identique.

**dehorter olivier** · 24/06/2011, 14h10

Envoyé par CCPMurat

t_personne_prs (prs_id, ....) = elus(Id_e,...)
R_prs_fun_pfu (prs_id, fun_id, ...) = assoce(A_id_elus#,A_id_fonctiono#)
t_fonction_fun (fun_id, fun_desc, ....) = lsf(Numero,Fonction)
Donc ma requête doit être :

Code :

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

1
2
3
4
5
SELECT *, lsf.Fonction 
FROM elus, lsf 
INNER JOIN assoce ON assoce.A_id_elus = elus.Id_e
INNER JOIN lsf fon ON numero = assoce.A_id_Fonctiono
WHERE lsf.Fonction LIKE 'a%'

Or j'ai une erreur

Pourquoi ?

Je suis sur du nom de la table et du nom de l'attribut. Id_e est lié à A_id_elus, ils sont de même type et de taille identique.

il y a un incohérence dans la requête. vous avez utilisé en MEME temps l'ancienne méthode "FROM elus, lsf " et la nouvelle (utilisant le INNER).
et une table a été oubliée !

Code :

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

1
2
3
4
SELECT *, lsf.Fonction 
FROM elus
   INNER JOIN assoce ON assoce.A_id_elus = elus.Id_e
   INNER JOIN lsf  ON lsf????.numero = assoce.A_id_Fonctiono

**punkoff** · 24/06/2011, 14h11

bonjour,

Déjà vous avez une jointure cartésienne en trop, et je pense que ça ne lui plait pas

Essayez comme ceci pour voir s'il y a encore une erreur ..

Code :

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

1
2
3
4
5
6
 
SELECT elu.*, fon.*
FROM elus elu
INNER JOIN assoce ass ON ass.A_id_elus = elu.Id_e
INNER JOIN lsf fon ON fon.numero = ass.A_id_Fonctiono
WHERE lsf.Fonction LIKE 'a%'

Problème utilisation des index

Langage SQL

Vue hybride

Discussions similaires

Partager

Partager