optimisation d'une requete de recherche

Version imprimable

Bonjour à tous
je fais une recherche selon un mot clé ($motCle) contenu dans un type de champs précisé par l'utilisateur a l'aide de radio button ($type)
la requete est sur plusieurs tables et dynamiquement construite

j'affiche dans un tableau les valeurs de retour, par groupe de 20 aidé par la pagination

Mon problème est le suivant, quand je faisais mes teste il n'y avait pas vraiment de probleme car j'avais environ une centaine d'entré dans ma table, sauf que j'ai du migré un ancien systeme et tous les dossier qu'il avait...ca porte présentement mon nombre d'enregistrement a 15000...la recherche est fastidieuse, le temps pour afficher les résultats est significatif maintenant...

La requete, testé en phpMyAdmin prend pratiquement 4 sec de temps d'execution moyen, je ne connais pas vraiment les temps requis ou habituel pour un nombre pareil d'enregistrement, mais bon est-ce normal ou encore c'est effectivement le temps que ca prend??

je mets ici ma requete pour vous en donner une idée
Les variables sont les suivante:
$_SESSION['typeRecherche'] = tbldossier.dossierNum
$_SESSION['motcle'] = ''; //Si c'es rien, ca cherche dans tous
$page = la page où on est rendu lors de l'affichage
$nb = le nombre d'affichage par page ..ici c'est 20
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 25 26 27 28 29 30 31 // Selection des enregistrements pour la page considérée $strScript = "SELECT SQL_CALC_FOUND_ROWS tbldossier.versionDossier, tblclientassure.clientAssurePrenom, tblclientassure.clientAssureNom,tbldossiercieassurance.numDossierAssurance, tbltiercepartie.prenomTier, tbltiercepartie.nomTier, tbldossier.dossierID, tblcieassurance.cieAssuranceNom, tbldossier.dossierNum, "; $strScript .= "(".$_SESSION['typeRecherche']." LIKE '".$_SESSION['motCle']."')*8+(".$_SESSION['typeRecherche']." LIKE '%".$_SESSION['motCle']."%') AS pertinence "; $strScript .= "FROM tbldossierajusteur "; $strScript .= "LEFT OUTER JOIN tbldossier ON tbldossierajusteur.dossierID = tbldossier.dossierID "; $strScript .= "LEFT OUTER JOIN tblclientassure ON tbldossier.clientAssurerID = tblclientassure.clientAssureID "; $strScript .= "LEFT OUTER JOIN tbldossiercieassurance ON tbldossier.dossierID = tbldossiercieassurance.dossierID "; $strScript .= "LEFT OUTER JOIN tblcieassurance ON tbldossiercieassurance.cieAssuranceID = tblcieassurance.cieAssuranceID "; $strScript .= "LEFT OUTER JOIN tbltiercepartie ON tbldossier.dossierID = tbltiercepartie.dossierID "; $strScript .= "WHERE tbldossier.dossierActif = 1 "; if($_SESSION['typeRecherche'] != "tblcieassurance.cieAssuranceNom"){ $strScript .= "AND tbldossiercieassurance.responsabilite = '100' "; } //$strScript .= "ORDER BY pertinence DESC, ".$_SESSION['typeRecherche']." "; $strScript .= "GROUP BY pertinence DESC, ".$_SESSION['typeRecherche']." HAVING pertinence > 0 "; //Modification de la requete d'affichage pour avoir que les enregistrements par tranche de $nb (20 ici) $strScript .= "LIMIT ".($page * $nb).",".$nb; echo $strScript; $requete = $objCon->dbQuery($strScript); // Nombre total d'enregistrements $script = "SELECT FOUND_ROWS()"; $request = $objCon->dbQuery($script); $total = mysql_fetch_row($request); ... //affichage
Donc, comment est ce qu'on fait pour rendre une recherh dans plusieur table d'une manière rapide et efficace??
j'attends de vos conseils encore une fois :roll:

P.S J'utilise mysql 4.1.20 et php 4.4.2
En guise d'information voici le nombre d'enregistrement de chaque table actuellement:
tbldossierajusteur -> 14 855
tbldossier -> 14 953
tblclientassure -> 11 873
tbldossiercieassurance -> 14 861
tblcieassurance -> 204
tbltiercepartie -> 13

13/11/2006, 23h29
Mr N.

Citation:

Envoyé par ozzmax

Donc, comment est ce qu'on fait pour rendre une recherh dans plusieur table d'une manière rapide et efficace??

Comment optimiser ses requetes :
1) Effectuer un EXPLAIN de la requête qui posent problème
2) Analyser les resultats
3) Poser les index en fonctions des resultats
4) Effectuer un ANALYZE sur toutes les tables qui font parties de la requête (le plus simple est de faire sur toute la base)
5) Rejouez les resultats pour voir si ca a amelioré quelque chose, recommencer en 1)
6) Effectuer régulièrement l'ANALYZE

http://dev.mysql.com/explain
13/11/2006, 23h51
ozzmax

oki merci pour cela, je me penche sur le mot clé Explain qui semble etre intéressant

tu peux me parler un peu des index et leur utilité?
14/11/2006, 09h36
Mr N.

Je ne peux pas en dire plus que la doc de mysql :
http://dev.mysql.com/doc/refman/5.0/...l-indexes.html
16/11/2006, 20h34
ozzmax

Salut j'ai lu les explications que tu m'as référé

Je comprends l'utilisation mais je ne sais pas comment définir les index?

comme c'est la, j'ai ajouter la commande suivante SELECT STRAIGHT_JOIN à ma requete plus haute...

ca améliore grandement le retour de résultats...je lisais que Mysql n'.utilisait pas les index si j'utilise le LIKE avec un joker devant le mot clé..c'est mon cas dans ma recherche...est ce qu'a ce moment je ne peux les utiliser?

Avec le simple ajout de STRAIGHT_JOIN je gagne significativement en temps d'execution...je passe de 4 sec a 0,15 sec... :)

Est-ce un coup de chance ou la l'instruction améliore grandement la manière de procédé?
16/11/2006, 22h57
Mr N.

Attention à ne pas bruler les étapes. Le straight_join court-circuite le travail de l'optimiseur. Pour moi c'est à utiliser en dernier recours, après avoir optimiser le reste et après avoir ajouter les index qui vont bien si besoin.
As-tu fais ce que j'ai donné dans mon premier post ?

Pour ajouter un index, il suffit de lire la doc :) ou d'utiliser phpMyAdmin
17/11/2006, 16h29
ozzmax

oki, je viens de comprendres comment ajouter des indexs
sauf que..est ce que je les mets sur les champs surlequel je fais des recherche?

Je ne sais pas comment ca fonctionne et j'ai lu les liens, mais je ne comprends pas vraiment plus dsl...

J'ai fait explain sur mes requetes, mais comment interpréter les résultats si tu ne sais pas comment ca fonctionne...:aie:
17/11/2006, 16h36
Mr N.

Oui il faut mettre des index là où tu fais des recherches ou là où tu fais des jointures.

Concernant le explain, c'est tout expliquer dans la doc :aie:
Colle ici les résultats qu'on t'aide à les analyser...

Oki c'est gentil

alors voici:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 EXPLAIN SELECT SQL_CALC_FOUND_ROWS tbldossier.versionDossier, tblclientassure.clientAssurePrenom, tblclientassure.clientAssureNom, tbldossiercieassurance.numDossierAssurance, tbltiercepartie.prenomTier, tbltiercepartie.nomTier, tbldossier.dossierID, tblcieassurance.cieAssuranceNom, tbldossier.dossierNum, ( tbldossier.dossierNum LIKE '' ) *8 + ( tbldossier.dossierNum LIKE '%%' ) AS pertinence FROM tbldossierajusteur LEFT OUTER JOIN tbldossier ON tbldossierajusteur.dossierID = tbldossier.dossierID LEFT OUTER JOIN tblclientassure ON tbldossier.clientAssurerID = tblclientassure.clientAssureID LEFT OUTER JOIN tbldossiercieassurance ON tbldossier.dossierID = tbldossiercieassurance.dossierID LEFT OUTER JOIN tblcieassurance ON tbldossiercieassurance.cieAssuranceID = tblcieassurance.cieAssuranceID LEFT OUTER JOIN tbltiercepartie ON tbldossier.dossierID = tbltiercepartie.dossierID WHERE tbldossier.dossierActif =1 AND tbldossiercieassurance.responsabilite = '100' GROUP BY pertinence DESC , tbldossier.dossierNum HAVING pertinence >0
me donne, en phpMyAdmin

Citation:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tbldossiercieassurance ALL NULL NULL NULL NULL 14884 Using where; Using temporary; Using filesort
1 SIMPLE tbldossier eq_ref PRIMARY PRIMARY 4 gdroldtest.tbldossiercieassurance.dossierID 1 Using where
1 SIMPLE tblclientassure eq_ref PRIMARY PRIMARY 4 gdroldtest.tbldossier.clientAssurerID 1
1 SIMPLE tblcieassurance eq_ref PRIMARY PRIMARY 4 gdroldtest.tbldossiercieassurance.cieAssuranceID 1
1 SIMPLE tbltiercepartie ALL NULL NULL NULL NULL 16
1 SIMPLE tbldossierajusteur ALL NULL NULL NULL NULL 15430 Using where

merci pour l'attention, c'est très apprécié :)

17/11/2006, 17h34
Mr N.

Peux-tu mettre le schéma de tes tables qui font parti de cette requete (sous phpmyadmin > export, juste la structure)
17/11/2006, 18h14
ozzmax

oui
voici:

Citation:

-- phpMyAdmin SQL Dump
-- version 2.6.1-pl3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Vendredi 17 Novembre 2006 à 12:13
-- Version du serveur: 4.1.20
-- Version de PHP: 4.4.2
--
-- Base de données: `gdroldtest`
--

-- --------------------------------------------------------

--
-- Structure de la table `tblcieassurance`
--

CREATE TABLE `tblcieassurance` (
`cieAssuranceID` int(11) NOT NULL auto_increment,
`cieAssuranceNom` varchar(50) NOT NULL default '',
`cieAssurancePhone` varchar(15) default NULL,
`assureurNoCivique` varchar(10) NOT NULL default '',
`assureurSuite` varchar(10) default NULL,
`assureurRue` varchar(30) NOT NULL default '',
`assureurVille` varchar(30) NOT NULL default '',
`assureurProvince` varchar(30) NOT NULL default '',
`assureurCP` varchar(10) NOT NULL default '',
`assureurActif` int(11) NOT NULL default '1',
`assureurPhone2` varchar(40) default NULL,
`assureurPhone3` varchar(40) default NULL,
`assureurPhone4` varchar(40) default NULL,
`assureurFax` varchar(40) default NULL,
`assureurEmail` varchar(50) default NULL,
`unique_id` varchar(15) default NULL,
PRIMARY KEY (`cieAssuranceID`),
UNIQUE KEY `unique_id` (`unique_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=369 ;

-- --------------------------------------------------------

--
-- Structure de la table `tblclientassure`
--

CREATE TABLE `tblclientassure` (
`clientAssureID` int(11) NOT NULL auto_increment,
`clientAssurePrenom` varchar(50) NOT NULL default '',
`clientAssureNom` varchar(50) NOT NULL default '',
`clientAssureNom2` varchar(40) default NULL,
`clientAssurePrenom2` varchar(40) default NULL,
`contact` varchar(50) NOT NULL default '',
`clientAssureActif` tinyint(4) NOT NULL default '1',
`noCivique` varchar(10) NOT NULL default '',
`rue` varchar(50) NOT NULL default '',
`ville` varchar(50) NOT NULL default '',
`province` varchar(25) NOT NULL default '',
`codePostal` varchar(15) NOT NULL default '',
`suite` varchar(10) default NULL,
`phoneMaison` varchar(15) NOT NULL default '',
`phoneCell` varchar(15) default NULL,
`phoneBureau` varchar(15) default NULL,
`unique_id` varchar(15) default NULL,
PRIMARY KEY (`clientAssureID`),
UNIQUE KEY `unique_id` (`unique_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=81812 ;

-- --------------------------------------------------------

--
-- Structure de la table `tbldossier`
--

CREATE TABLE `tbldossier` (
`dossierID` int(11) NOT NULL auto_increment,
`dossierNum` varchar(20) NOT NULL default '',
`versionDossier` varchar(20) NOT NULL default '',
`clientAssurerID` int(11) NOT NULL default '0',
`typeSinistreID` int(11) NOT NULL default '0',
`categorieID` int(11) NOT NULL default '0',
`datePerte` date NOT NULL default '0000-00-00',
`civiquePerte` varchar(10) NOT NULL default '',
`suitePerte` varchar(10) NOT NULL default '',
`ruePerte` varchar(25) NOT NULL default '',
`villePerte` varchar(35) NOT NULL default '',
`provincePerte` varchar(15) NOT NULL default '',
`cpPerte` varchar(10) NOT NULL default '',
`ouvertureDossier` datetime NOT NULL default '0000-00-00 00:00:00',
`derniereModification` datetime default NULL,
`dossierActif` smallint(6) NOT NULL default '0',
`dateRappel` date NOT NULL default '0000-00-00',
`unique_id` varchar(15) default NULL,
PRIMARY KEY (`dossierID`),
UNIQUE KEY `unique_id` (`unique_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=73951 ;

-- --------------------------------------------------------

--
-- Structure de la table `tbldossierajusteur`
--

CREATE TABLE `tbldossierajusteur` (
`dossierAjusteurID` int(11) NOT NULL auto_increment,
`ajusteurID` int(11) NOT NULL default '0',
`dossierID` int(11) NOT NULL default '0',
PRIMARY KEY (`dossierAjusteurID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14856 ;

-- --------------------------------------------------------

--
-- Structure de la table `tbldossiercieassurance`
--

CREATE TABLE `tbldossiercieassurance` (
`dossierCieAssuranceID` int(11) NOT NULL auto_increment,
`dossierID` int(11) NOT NULL default '0',
`cieAssuranceID` int(11) NOT NULL default '0',
`numDossierAssurance` varchar(25) NOT NULL default '',
`policeAssurance` varchar(25) NOT NULL default '',
`responsabilite` decimal(5,2) unsigned NOT NULL default '0.00',
`souscription` decimal(5,2) NOT NULL default '0.00',
`reviseurID` int(11) NOT NULL default '0',
PRIMARY KEY (`dossierCieAssuranceID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14862 ;

-- --------------------------------------------------------

--
-- Structure de la table `tbltiercepartie`
--

CREATE TABLE `tbltiercepartie` (
`tiercePartieID` int(11) NOT NULL auto_increment,
`nomTier` varchar(30) NOT NULL default '',
`prenomTier` varchar(30) NOT NULL default '',
`civiqueTier` varchar(10) NOT NULL default '',
`suiteTier` varchar(10) NOT NULL default '',
`rueTier` varchar(25) NOT NULL default '',
`villeTier` varchar(35) NOT NULL default '',
`provinceTier` varchar(30) NOT NULL default '',
`cpTier` varchar(10) NOT NULL default '',
`dossierID` int(11) NOT NULL default '0',
PRIMARY KEY (`tiercePartieID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;
17/11/2006, 19h24
Mr N.
Afin de voir les effets bénéfiques de tes modifications, n'oublie pas de noter le temps mis à mysql pour executer ta requete, phpmyadmin te le dit ("query took xxx" ou un truc dans ce genre).
(N'oublie pas aussi que mysql utilise un cache qui fait que la première execution est plus lente que les suivantes)

Commençons d'abord par les deux ALL à la fin du explain.
Comme il est dit dans la doc :

Citation:

ALL
A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

Il faut que tu ajoutes des indexes sur tes colonnes qui servent dans les jointures.
Donc ajoute un index sur tbldossierajusteur.dossierID et sur tbltiercepartie.dossierID
C'est pas compliqué, quelques cliques sous phpMyadmin suffisent.
Ensuite et c'est très important, il faut que tu fasse un analyze table :
Code:

1 2 3 4 5 6 ANALYZE TABLE tbldossiercieassurance; ANALYZE TABLE tbldossier; ANALYZE TABLE tblclientassure; ANALYZE TABLE tblcieassurance; ANALYZE TABLE tbltiercepartie; ANALYZE TABLE tbldossierajusteur;
Le mieux etant de le faire sur toute la base, en ligne de commande :

Code:

mysqlcheck -Aa -u root -p
17/11/2006, 19h48
ozzmax

oki c'est fait

donc c'est seulement avec ces quelque modification que la requete est améliorée incroyablement
j'en remarque déjà les résultats

Donc j'ai a refaire explain?
je l'ai fait et voici ce que ca retourne

Citation:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tbldossiercieassurance ALL NULL NULL NULL NULL 14834 Using where; Using temporary; Using filesort
1 SIMPLE tbldossier eq_ref PRIMARY PRIMARY 4 gdroldtest.tbldossiercieassurance.dossierID 1 Using where
1 SIMPLE tblclientassure eq_ref PRIMARY PRIMARY 4 gdroldtest.tbldossier.clientAssurerID 1
1 SIMPLE tbldossierajusteur ref dossierID dossierID 4 gdroldtest.tbldossier.dossierID 1 Using index
1 SIMPLE tblcieassurance eq_ref PRIMARY PRIMARY 4 gdroldtest.tbldossiercieassurance.cieAssuranceID 1
1 SIMPLE tbltiercepartie ref dossierID dossierID 4 gdroldtest.tbldossier.dossierID 1

Je demande ici un conseil

Si l'usagé n'entre aucun mot clé comme recherche...est ce que je devrais modifier ma requete en conséquent pour éviter l'utilisation du LIKE dans ma requete

ainsi j'aurais un truc du genre:

Code:

1
2
3
4
5
6
7
8
9
 
if($_SESSION['motCle'] == ''){
  //Pas de mot clé spécifié...on retourne tout selon le type choisi
  $strScript = "SELECT SQL_CALC_FOUND_ROWS tbldossier.versionDossier, tblclientassure.clientAssurePrenom, tblclientassure.clientAssureNom,tbldossiercieassurance.numDossierAssurance, tbltiercepartie.prenomTier, tbltiercepartie.nomTier, tbldossier.dossierID, tblcieassurance.cieAssuranceNom, tbldossier.dossierNum ";
}
else{
  //Un mot clé a été spécifié
  $strScript = "SELECT SQL_CALC_FOUND_ROWS tbldossier.versionDossier, tblclientassure.clientAssurePrenom, tblclientassure.clientAssureNom,tbldossiercieassurance.numDossierAssurance, tbltiercepartie.prenomTier, tbltiercepartie.nomTier, tbldossier.dossierID, tblcieassurance.cieAssuranceNom, tbldossier.dossierNum, (".$_SESSION['typeRecherche']." LIKE '".$_SESSION['motCle']."')*8+(".$_SESSION['typeRecherche']." LIKE '%".$_SESSION['motCle']."%') AS pertinence ";
}

Où avec l'optimisation des table ca devient inutile de faire cette validation?

17/11/2006, 20h17
Mr N.

Si c'est tres bien. le like n'est pas performant du tout, autant s'en servir que quand c'est nécessaire !
(Voir meme utiliser les recherches full text de mysql)
17/11/2006, 20h32
ozzmax

Je te remerci grandement de cette intervention
j'ai beaucoup appris aujourd'hui avec ces index et la performance que ceux ci offrent aux requetes

Merci d'avoir été patient avec moi afin de m'expliquer le tout

bonne fin de semaine ;)
17/11/2006, 20h38
Mr N.

J'ai pas expliqué grand chose, je n'ai fait que rediriger vers la doc. Mais au moins elle est complete est en prenant le temps de comprendre on y arrive tout seul.
N'oublie pas de faire le analyze régulièrement...

Pour te donner une idée de l'apport des index, multiplie les chiffres de la colonnes rows dans le explain ;)