Problème utilisation des index

Version imprimable

2 pièce(s) jointe(s)

Bonjour,

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

Code:

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:

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:

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:

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

22/06/2011, 17h44
dehorter olivier

Bonjour

la fonction des index n'est pas de "simplifier" les requêtes mais de les accélérer :mouarf:
En faisant un index sur plusieurs colonnes, cela ne crée pas une colonne synthétique :mrgreen:

Je note ta remarque 1 :cry:

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

Citation:

WHERE `Fonction` LIKE 'a%'

????
22/06/2011, 21h10
punkoff

Citation:

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%'"
23/06/2011, 14h56
CCPMurat

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.
http://192.168.0.102/elusfonc.jpg
23/06/2011, 15h05
punkoff
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:

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 :)
24/06/2011, 13h58
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:

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

Citation:

#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.
24/06/2011, 14h10
dehorter olivier
Citation:
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:

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

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
24/06/2011, 14h11
punkoff
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:

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%'
27/06/2011, 11h32
CCPMurat

J'ai trouvé une alternative à mon problème pour les index, elle est similaire à celle de punkoff et elle nécessite une requête avec plusieurs conditions mais ça résout mon problème.

Merci de vos réponse.