mercurey Posted November 26, 2012 Share Posted November 26, 2012 (edited) Bonjour à tous, J'utilise Mysql et j'ai un requete qui rame de temps en temps. La plupart du temps elle met 0.02s à s’exécuter mais des fois ça monte à 25s, je ne comprends pas. SELECT SQL_CALC_FOUND_ROWS idhabit, telhab, ville, departement, numero, loc, dated, cc, piece, surface, descriptif, descen, descde, url, locasais, demande, idpass, datevignette, datemaj, nbphotos, h.type, h.prix, 0 AS trilastFROM habitation h, personne pWHERE h.telhab = p.telpers && h.visible =1 && p.visible =1 && ( h.cpdep = '24' ) && ( h.type = 'maison' ) && h.demande = 'annonce' && h.loc = 'vente' && h.prix >=50000 && h.prix <=70000ORDER BY trilast ASC , prix DESCLIMIT 50 , 10 id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra1 | SIMPLE | h | ref type,prix,demande,visible,telhab,loc,demande_2,cpdep | cpdep | 5 | const 6976 Using where; Using filesort1 | SIMPLE | p | eq_ref telpers_2,telpers,visible | telpers | 32 | lesclesd_db.h.telhab | 1 | Using where est ce que l'un de vous aurai une idée ? merci Edited November 26, 2012 by mercurey Link to comment Share on other sites More sharing options...
Dadou Posted November 26, 2012 Share Posted November 26, 2012 As tu mis un index sur les colonnes de tes critères, avec un index, cela tournera bien plus vite Link to comment Share on other sites More sharing options...
mercurey Posted November 26, 2012 Author Share Posted November 26, 2012 Oui, c'est vrai que sans index, là, je seais à 1min / requete ;o) j'ai mis la sortie du "explain" justement après la requête, mais c'est pas forcement facile de l’interpréter. Link to comment Share on other sites More sharing options...
Dadou Posted November 26, 2012 Share Posted November 26, 2012 Essai en faisant une jointure pour la table personne, pour que le critère de rapprochement ne soit pas dans le where, une jointure, c'est normalement plus rapide SELECT SQL_CALC_FOUND_ROWS idhabit, telhab, ville, departement, numero, loc, dated, cc, piece, surface, descriptif, descen, descde, url, locasais, demande, idpass, datevignette, datemaj, nbphotos, h.type, h.prix, 0 AS trilastFROM habitation h join personne p on h.telhab = p.telpersWHERE h.visible =1 && p.visible =1 && ( h.cpdep = '24' ) && ( h.type = 'maison' ) && h.demande = 'annonce' && h.loc = 'vente' && h.prix >=50000 && h.prix <=70000ORDER BY trilast ASC , prix DESCLIMIT 50 , 10 Link to comment Share on other sites More sharing options...
mercurey Posted November 26, 2012 Author Share Posted November 26, 2012 Bonne idée, mais apparemment moins performant (en tout cas dans mon cas), je tourne à 0.03s (contre 0.02 avant), j'ai également eu un 12s sur les 10 tests. le "explain" ressort exactement la même chose Link to comment Share on other sites More sharing options...
Dadou Posted November 26, 2012 Share Posted November 26, 2012 Une jointure est normalement plus rapide, puisque dans ta requête, il y a un produit cartésien de fait alors que dans la mienne non. Pour une requête aussi simple, pour moi il ne devrait pas y avoir de lenteurs, quand c'est le cas, il y a un problème d'index, je pense qu'il doit en manquer Link to comment Share on other sites More sharing options...
mercurey Posted November 26, 2012 Author Share Posted November 26, 2012 Tous les champs sont en index, c'est ce qui me pose problème et comme tu dis, c'est une requête très simple qui rame "seulement" à peux près 1 fois sur 10, sinon, elle va très bien. si il y a un problème d'index, est ce qu'on devrait pas le voir avec le "explain" ? merci pour ton aide Link to comment Share on other sites More sharing options...
Dadou Posted November 26, 2012 Share Posted November 26, 2012 La longueur de ta clé telpers est vraiment à 32 ? ça me parait grand comme longueur d'index Link to comment Share on other sites More sharing options...
mercurey Posted November 26, 2012 Author Share Posted November 26, 2012 Mince, tu as raison, j'avais pas fait gaffe à ça, tu crois que ça peux avoir une incidence sur le fait que ça rame aléatoirement ? Link to comment Share on other sites More sharing options...
Dadou Posted November 26, 2012 Share Posted November 26, 2012 Oui, plus l'index est grand, plus il met de temps a chercher, après, il y a le cache SQL qui peut jouer Link to comment Share on other sites More sharing options...
jcaron Posted November 26, 2012 Share Posted November 26, 2012 Si je décode correctement les choses, tu as des index sur chaque colonne, plutôt qu'un (ou plusieurs) index sur plusieurs colonnes? Si c'est bien le cas, suivant la répartition des données dans la table, c'est pas du tout évident qu'aucun index n'aide tant que ça, et en plus ça complique le boulot de mysql pour trouver quel index il va utiliser. Dans ton exemple, il se pose donc la question de quel index il va utiliser: celui sur type, celui sur prix, celui sur demande, etc. Il finit par choisir celui sur cpdep (probablement parce qu'au vu des infos qu'il a, c'est le plus "discriminant"). Ça lui donne 6976 lignes, qu'il faut qu'il lise toutes, ensuite il trie tout ça (en utilisant un fichier temporaire), puis il fait le LIMIT, puis pour chacune de ces lignes, il faut qu'il fasse la jointure avec l'autre table. Ça représente une tonne d'I/O. Ta requête va vite quand les parties utiles de la table sont entièrement dans le cache de l'OS et que les E/S de ton disque ne sont pas saturées, mais il suffit de peu pour que ça soit éjecté du cache (ou que ce soit une requête sur une partie moins utilisée et donc pas en cache), et hop, il faut qu'il recharge tout ça depuis le disque, ce qui prend nettement plus de temps. Il peut aussi arriver qu'en fonction des valeurs des critères, il décide d'utiliser un autre index qui se révèle être moins discriminant, et l'oblige à charger encore plus de données. Plusieurs solutions: - la première, c'est au minimum d'ajouter les critères de tri dans chaque index susceptible d'être utilisé (ici ça donnerait un index sur cpdep,prix). Comme ça, il lit les lignes directement dans le bon ordre, et il peut s'arrêter dès qu'il en a assez (en tous cas postgresql le ferait, je suppose que mysql aussi) - la deuxième, c'est d'avoir un ou plusieurs index qui incluent plusieurs des colonnes qui sont utilisées comme critère. Si par exemple tu as forcément tous ces critères dans ton WHERE, un index sur cpdep,type,demande,loc,visible,prix serait idéal. Si c'est plus souple que ça il va falloir adapter un peu. L'ordre des colonnes dans l'index peut avoir pas mal d'importance aussi, suivant le niveau de "discrimination" de chaque critère. _AT_Dadou: Heureusement, un serveur SQL décent utilise généralement le même plan pour une jointure explicite (a JOIN b ON a.c=b.d) et une jointure implicite (a,b WHERE a.c=b.d) tant qu'il n'y a pas de OUTER JOIN dans les parages. D'ailleurs ça se voit bien dans le plan choisi: il utilise un index sur la première table pour limiter le produit cartésien à faire, donc il utilise le critère avant le produit cartésien, pas après. Au final, sauf cas particuliers, c'est plus une question de lisibilité qu'autre chose. Ça permet aussi de ne pas oublier un critère de jointure, et ça permet de permuter entre un INNER et un OUTER JOIN plus facilement (et aussi de mélanger les différents types de JOINs dans la même requête, parce que dans ce cas, un critère dans le WHERE et un dans le ON ne veulent pas du tout dire la même chose). Jacques. Link to comment Share on other sites More sharing options...
Dadou Posted November 27, 2012 Share Posted November 27, 2012 Malheureusement Mysql n'est pas un serveur SQL décent et il merdouille quelque peu sur les optimisations (du moins jusqu’à sa version 5.1), en l'occurence, quand tu commences a avoir du volume, tu t’aperçois vite qu'il y a une réelle différence entre la jointure explicite, et l'implicite. Il merdouille aussi pas mal quand tu met une requête dans un "in". Au boulot, avec la volumétrie que l'on a, on titille pas mal les limites de Mysql, on s'arrache parfois les cheveux, sur des points que Postgres gérerait sans aucun soucis. Link to comment Share on other sites More sharing options...
jcaron Posted November 27, 2012 Share Posted November 27, 2012 Le planner de mysql est probablement nettement plus simpliste que celui de postgresql, mais j'espère quand même que sur un cas aussi facile il retombe sur ses pattes :-) D'ailleurs le plan choisi montre que c'est le cas, c'est juste que les index ne sont pas suffisants pour arriver à un bon résultat. Jacques. Link to comment Share on other sites More sharing options...
mercurey Posted November 27, 2012 Author Share Posted November 27, 2012 (edited) Merci beaucoup à vous 2, je vais donc me re-pencher sur les index. Tous les champs utilisé sont bien en index, mais pas forcement en index multiple, je penses qu'il serait plus néfaste que positif de prévoir tous les cas. Cette requete sort du moteur de recherche et varie d'une recherche à l'autre. Est ce que si je met un index multiple sur "cpdep,type,demande,loc,visible" qui est la base, ce sera mieux ? sachant qu'il y aura toujours en plus soit le prix et/ou la surface et/ou ... +++ j'ai créé des index multiples "generique", ont va voir Edited November 27, 2012 by mercurey Link to comment Share on other sites More sharing options...
mercurey Posted November 27, 2012 Author Share Posted November 27, 2012 le nouvel explain : id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra1 | SIMPLE | h | ref | type,prix,demande,visible,telhab,loc,demande_2,cpd... | type_2 | 40 | const,const,const | 4516 | Using where; Using filesort1 | SIMPLE | p | eq_ref | telpers_2,telpers,visible | telpers | 32 | lesclesd_db.h.telhab | 1 | Using where cela devrait apporter qq chose vu qu'il travail maintenant sur 4516 lignes au lieux de 6976 lignes Link to comment Share on other sites More sharing options...
jcaron Posted November 27, 2012 Share Posted November 27, 2012 C'est mieux, mais c'est loin d'être optimal, surtout qu'il utilise toujours un filesort. Idéalement, avec les bons index, il ne devrait avoir besoin que de charger les N premières lignes nécessaires, et ne pas faire de filesort. Tu as combien de critères optionnels et combien de critères de tri possibles? Jacques. Link to comment Share on other sites More sharing options...
mercurey Posted November 29, 2012 Author Share Posted November 29, 2012 Désolé pour la réponse tardive. J'ai 7 critères optionnels ou variables (je peux choisir 4 pièces ou 4 et 5 et 6 pièces par exemple) et 4 critères de tri * desc et asc Link to comment Share on other sites More sharing options...
jcaron Posted November 29, 2012 Share Posted November 29, 2012 Effectivement là ça risque d'être difficile de faire des index pour toutes les combinaisons de critères. Ceci dit, suivant la proportion de lectures et d'écritures, ça peut être tout à fait "rentable". Sinon tu peux essayer de faire des stats sur les combinaisons les plus courantes, et mettre les index correspondants pour voir ce que ça donne. Jacques. Link to comment Share on other sites More sharing options...
mercurey Posted December 4, 2012 Author Share Posted December 4, 2012 Merci Jacques, j'ai fait le tests avec plusieurs index multiples de "base" les plus courant, j'ai l'impression que c'est un petit peux mieux, mais c'est pas encore ça. Ça rame toujours aléatoirement de temps en temps. to be continued ... Link to comment Share on other sites More sharing options...
Dadou Posted December 4, 2012 Share Posted December 4, 2012 Si ça rame encore, il est peut être nécessaire de repenser ton schéma de base de données Ou de monitorer les requêtes lentes, comme cela, tu sauras précisément, avec quels critères la requête est plus longue Link to comment Share on other sites More sharing options...
jcaron Posted December 4, 2012 Share Posted December 4, 2012 Une méthode qui permet quand tout va bien de réduire les temps d'accès, c'est de séparer une table avec des champs importants en deux. Par exemple, pour tes annonces, tu fais une première table avec les colonnes qui correspondent aux critères de filtrage et de tri (département, prix, type, date, etc.), et une deuxième table avec les colonnes qui ne sont pas utilisées pour le filtrage ou le tri et qui peuvent être plus longues (titre, description, etc.). Comme ça, si le planner SQL fait bien son travail, il n'a besoin d'accéder qu'à la première table (qui est beaucoup plus petite et donc a plus de chances de rester en cache) pour déterminer les lignes à retourner, et ensuite seulement il va piocher les quelques lignes dont il a besoin dans l'autre table. Mais quoi qu'il arrive, si tu as plus de données que de RAM, il y a un moment où des bouts de table ne vont pas être en RAM, et il va falloir aller les chercher sur le disque, ce qui est forcément plus lent. Jacques. Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now