Bonjour j'implémente un moteur de recherche avancé pour un site web et voici ma requête sql  
 
SELECT DISTINCT spip_articles.titre AS t, spip_articles.id_article,spip_articles.id_rubrique,j.titre o, e.titre v, u.titre a, spip_documents.contenu AS c,spip_documents.fichier, u.id_secteur, spip_auteurs.nom AS d,spip_mots.titre AS f FROM spip_articles
        LEFT JOIN  spip_auteurs_articles  ON spip_auteurs_articles.id_article= spip_articles.id_article
        LEFT JOIN spip_auteurs  ON spip_auteurs.id_auteur= spip_auteurs_articles.id_auteur
        LEFT JOIN  spip_rubriques u  ON  u.id_rubrique = spip_articles.id_rubrique
        LEFT JOIN  spip_rubriques e  ON  u.id_parent = e.id_rubrique
        LEFT JOIN  spip_rubriques j  ON  e.id_parent = j.id_rubrique
        LEFT JOIN spip_mots_articles  ON spip_mots_articles.id_article=spip_articles.id_article
        LEFT JOIN spip_mots  ON spip_mots.id_mot=spip_mots_articles.id_mot
        LEFT JOIN spip_documents_liens ON spip_documents_liens.id_objet=spip_articles.id_article
        LEFT JOIN spip_documents ON spip_documents.id_document=spip_documents_liens.id_document
        WHERE ((spip_articles.titre LIKE '".$_GET['mot']."' OR spip_articles.chapo LIKE '".$_GET['mot']."'' OR spip_auteurs.nom LIKE '".$_GET['mot']."' OR u.titre like '".$_GET['mot']."' OR match(spip_documents.contenu) against ('".$_GET['mot']."' IN boolean mode))"
 
au moment de l'exécution sa prend du temps "57s" alors si quelqu'un a une idée sur l'optimisation de cette requête je serai très reconnaissante

facile
tu enlèves le distinct,  tu évites les likes et les left tu les remplaces par des inner aussi...  
des lefts joins sur des tables "de reference"...
comme sur le left join spip_rubriques u on u...  
cela veux dire que tu veux récupérer le titre qui est stocké dans cette table,  et donc un inner join sera déjà plus "logique" (par forcement plus performant, mais plus "logique"
 à part cela tes jointures semblent "normales" sauf que
si dans ta clause where tu fais des sélections sur un champ d'une table en left outer join... cela correspond directement à un inner join
 
Puis après si tu n'as pas des index à créer sur tes tables
sur spit_article (titre, chapo) et pareil sur les autres tables et champs correspondants de ta clause where...
 
le  dernier critere de selection (match(...,Boolean mode) :
 je ne connais pas ce que cela fait... mais en générale le transtypage (si cela correspond à un changement en bolean...) n'est pas des plus performant en sql...
 
Le mieux, c'est de regarde si tu n'as pas dans ton éditeur de SQL, un outil qui te permette d'optimiser ta requête plus "précisément".
 
 
Le distinct te sert à quoi? généralement , tu utilises un distinct pour ne pas avoir des doublons... donc cela veut dire que tu remontes plus de donner qu'attendu et donc peut etre:
des jointures pas "correctes" ou des sélections pas encore assez restrictives...
 
Guillaume
 

mercii pour vos remarques le match against permet de rechercher dans l'index full text et puis toutes mes tables sont indexé
et  comment je puisse faire pour éviter les like
 
pour les doublons j'ai utilisé le distinct parce que l'affichage des articles se repete au nombre d'auteurs et de mots clés associés ...

Elles sont indexées sur les ID, mais pas forcement sur les champs qui sont dans ta clause where? non?
Pour les likes, je plaisantais, il y a des "moments" où tu ne peux pas t'en passer... mais c'est pas ce qui est l'opérateur le plus performant...
Pour résoudre ton soucis de distinct, je ne vois pas trop, car je ne connais pas ta BD...

si tous mes champs sont indexées

pour les doublons vous avez une idée!!

quel sont les informations sur ma BD que vous voulez savoir

pas d'optimisateur sur ton SQL?  
en fait... je viens de relire ta clause where
et  j'ai peur que c'est la requete que tu veux faire qui n'est pas assez restrictive...
tu "scannes" ta base en entier car tu cherches la chaine de caractere $_GET['mot'] dans le champ  
spip_articles.titre ,  
spip_articles.chapo,  
spip_auteurs.nom,
u.titre  
et
spip_documents.contenu
donc tu ne sais pas trop ce doit faire ta requête...
tu fais une recherche par mot clé, par auteur, par titre...
là elle fait tout en une seule fois.. mais "lentement"...
Ne serait il pas plus simple de faire dans ton appli X types de sélection bien définie (et tu n'auras plus alors des doublons, peut être)
 
Pour le distinct il faut peut être le laisser .
 
NB.: As tu pensé à la casse de ta sélection? une recherche avec like c'est Keysensitf... Mot <> mot

bein je realise un moteur de recherche avancée la requête que j'ai publié c'est pour le premier champ qui permet de faire la recherche en générale et puis j'ai d'autres champs pour spécialiser

si tu as tous qui est indexés, "correctement" et que tu dois faire cette requête quand même...
Je ne vois plus trop comment t'aider...
A part de "spliter" ta requête et de faire une autre requête reprennant les résultats des différentes étapes...
tu peux gagner des secondes comme ça...
 
 
 

merci quand même  

ben tu fais un système de cache par requête, ça prend de la place en disque dur, mais de nos jours les Go ne coutent pas cher  
 
A moins que le contenu change beaucoup comme dans un forum, c'est la bonne solution et tu indexe aussi les mots un par un dans des tables pour des recherches non enregistré mais avec le bon mot. (dans sans le like)
 

Les instructions "OR" aussi font mal au SGBD. Voir si t'as pas un autre moyen de formuler ta recherche, genre faire 3 requêtes (une pour chaque OR) reliées par un UNION et pour éviter les doublons, tu fais un group BY sur les champs permettant d'éliminer les doublons (j'imagine, le titre).

Comment ça marche un index sur un "mot"?
Guillaume

ben tu split le texte et tu le classe dans une table "mots" et tu créé une autre table pour lier ce mot au contenu.
 
 

@Fabien :
wahoo! fallais aller la chercher cette solution !!!
Ca sent la "bidouille" vécue?  
Mais ç'a l'air d'être pas mal (il faut que je la retienne, celle-ci)  
Juste que la gestion de ta table mot-contenu , si tu as du turn-over, cela doit être vite chaud à gérer...
Guillaume

bof, c'est la base de l'indexation, en quelque sorte, tu créé la recherche "mot" et tu l'indexe dans une table, ce que font tous les moteurs de recherche avancé, le "like" c'est pour des recherches ponctuel pour l'admin ou pour de très petites tables.
le "like" c'est ce qui peut te tuer un serveur si tu as du traffic et une bdd assez grande (forum, etc)

C'est déjà fait ici ça, le match ... against = full text index, mais ça ne vaut en général pas la peine de le faire quand il n'y a que quelques mots par champs.
 
Elle fait quelle taille ta DB? Enfin +- combien de lignes par table dans tes joins?
57s même pour des tables avec beaucoup de lignes ça me parait beaucoup.
 
Sans avoir les tables ici pour tester c'est difficile de t'aider plus que ce qui a déjà été dit:
 - Revoir si les indexes sont correct (aussi suffisamment couvrant ou pas trop couvrant).
 - Split de la query en 5 et seulement après faire les dernier JOIN commun a tout le monde.
 - Revoir le design de la DB, peut être arrives-tu a un point ou tu dois commencer a dé-normaliser certaines tables.
 - Faire ce qu'il faut pour enlever le Distinct (et pas le remplacer par un group by hein). Si tu as besoin d'un distinct c'est que ton WHERE ou tes JOIN ne sont pas assez restrictif. Tu peux probablement faire d'une pierre deux coup en enlevant le DISTINCT et en fesant un split de ta query.
 
Les quick win c'est le split de la query et le distinct (le but étant de virer un maximum de LEFT JOIN et de ne les laisser que sur un dataset le plus petit possible, d'ou le split en plusieurs query). La de-normalisation et fine tuning d'index ça ne fonctionne que quand le query de base est optimisée au maximum.

Pour éliminer les doublons (avec le distinct), tu peux faire un group by : t'auras le même résultat sauf que le group by est beaucoup plus rapide