Problème utilisation des index [Résolu]

[CCPMurat]

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

[dehorter olivier]

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]

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]

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]

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

[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

#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]

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 :

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 :

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]

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%'

[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.