union très lent

[fpouget]

Bonjour,

J'ai deux tables très différentes sur lesquelles je veux faire un UNION pour pouvoir crées des flux xml.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
(
SELECT p.id AS pid, p.message, p.poster, p.posted, t.id, t.subject, t.num_replies, t.last_post, f.id AS fid, f.forum_name, f.quickpost, c.cat_name
FROM posts AS p
INNER JOIN topics AS t ON p.id = t.last_post_id
INNER JOIN forums AS f ON f.id = t.forum_id
INNER JOIN categories AS c ON f.cat_id = c.id
LEFT JOIN forum_perms AS fp ON ( fp.forum_id = f.id
     AND fp.group_id =3 )
WHERE (
     fp.read_forum IS NULL
     OR fp.read_forum =1
     )
     AND t.moved_to IS NULL
)
UNION 
(
SELECT id AS pid, description AS message, 'p' AS poster, date_validation AS posted, id, titre AS subject, 'n' AS num_replies,date_validation AS last_post, 999 AS fid, 'f' AS forum_name, 'q' AS quickpost, 'c' AS cat_name
FROM annu_sites
WHERE activation =1
)
ORDER BY last_post DESC
LIMIT 20

La réquête marche, mais est beaucoup trop longue + de 3 secondes alors que si j'exécute les deux requêtes séparément j'ai moins de 0.5 secondes pour la première et moins de 0.1 pour la deuxième.
0.5 + 0.1 != 3 ... Est-ce normal ? Comment accélerer ma requête principale ?

Je suis avec mysql 4.1

Merci

[MrPink]

Salut,

Oui cela semble normal que ta requête soit plus longue à executer, car ici :

- Pour chaque lignes de ta requête 'principale', tu fais un accès sur la sous-requete. (Ex : 0.5 + 0.1 = 0.6 mais 0.5 + 0.1*nb_lignes ça fait plus)

Je ne sais pas vraiment quoi te conseiller pour améliorer cela mais par exemple pour Oracle j'utilise Toad qui m'analyze les requetes et m'affiche les 'full access table', ça peut aider. Je ne sais pas si Toad pour MySQL ou MySQL Administrator le font.

@+

[fpouget]

Comprends pas ! Quelle sous-requête ?

J'ai essaye de réduire le nb d'élément remonté par la requête 2 (de 1000 à 4) en ajoutant une condition, mais cela ne change rien au temps de calcul ...
Des idées ?

[jp_rennes]

Je veux bien croire que chaque requête séparemment est rapide.
je ne sais pas combien de lignes sont ramenées par ta requête mais :
- le order by à la fin est très gourmand en calcul
- le union par défaut élimine les lignes doublons et ça aussi c'est couteux

La seule piste que je peux te suggérer est de placer un 'explain' devant ta requête pour voir comment celle-ci est évaluée.

[fpouget]

Envoyé par jp_rennes

- le order by à la fin est très gourmand en calcul

non pas tant que ça, je gagne un peu en l'enlevant, mais vraiment pas grand chose ...

Envoyé par jp_rennes

- le union par défaut élimine les lignes doublons et ça aussi c'est couteux

La par contre tu as raison en limitant le nb de retour dans les 2 requêtes je repasse sous les 0.7 secondes. C'est bien mais ce n'est pas très satisfaisant . N'y a t'il pas un moyen de contraindre Union d'enlever les doublons ? Ou peut-etre est-ce une autre commande ?

Merci

Edit : Vu UNION ALL mais cela ne change rien par rapport à UNION à partir du momment ou les deux requêtes ne retourne qu'un numbre limité de lignes. En mettant UNION ALL à la place de UNION sur la requete initiale on gagne environ 1 seconde mais ce n'est pas assez performant.

Un explain de la requete avec UNION ALL me donne ça

Code :

1
2
3
4
5
6
7
8
9
 
id      select_type    TABLE        type    possible_keys                            KEY                    key_len    ref               rows    Extra          
1       PRIMARY        f            ALL     PRIMARY                                  NULL                   NULL       NULL              27                     
1       PRIMARY        t            ref     topics_forum_id_idx,topics_moved_to_idx  topics_forum_id_idx    4          f.id              1357    USING WHERE    
1       PRIMARY        p            eq_ref  PRIMARY                                  PRIMARY                4          t.last_post_id    1                      
1       PRIMARY        c            eq_ref  PRIMARY                                  PRIMARY                4          f.cat_id          1                      
1       PRIMARY        fp           eq_ref  PRIMARY                                  PRIMARY                8          const,f.id        1       USING WHERE    
2       UNION          annu_sites   ALL     NULL                                     NULL                   NULL       NULL              1222    USING WHERE    
NULL    UNION RESULT   <union1,2>   ALL     NULL                                     NULL                   NULL       NULL              NULL    USING filesort

Qq'un peut m'expliquer ?

[pcaboche]

Je ne connais pas les détails liés à l'implémentation mais as-tu essayé de:

1) créer une table temporaire, faire un INSERT IGNORE INTO... SELECT (IGNORE = à cause des doublons) avant de fair un SELECT * FROM table_temp ORDER BY ... ?

2) D'avoir 2 curseurs qui parcours les résultats des 2 SELECT (triés) tout en s'assurant que le résultat reste ordonné et ne comporte pas de doublons ?

[fpouget]

Envoyé par pcaboche

Je ne connais pas les détails liés à l'implémentation mais as-tu essayé de:

1) créer une table temporaire, faire un INSERT IGNORE INTO... SELECT (IGNORE = à cause des doublons) avant de fair un SELECT * FROM table_temp ORDER BY ... ??

Non pas encore mais je vais tester, cependant je n'y crois pas trop vu que ce n'est pas l'order by qui prend le plus de temps ...
A moins de faire l'insertion dans la table temp en deux temps puis un troisième requête pour faire le order --> cela me semble mieux sachant que je peux mettre un limit dans les deux premiers cela limitera de fait le nb de lignes à traiter. Mais passer par une table temporaire ce n'est pas très propre non ?
Je vais faire les deux tests et je vous donnerai les résultats

Envoyé par pcaboche

2) D'avoir 2 curseurs qui parcours les résultats des 2 SELECT (triés) tout en s'assurant que le résultat reste ordonné et ne comporte pas de doublons ?

Je crois que je comprends le concept : je stocke les resultats des deux select chacun dans un tableau (en PHP) et ensuite je trie les éléments des deux tableaux pour avoir mes 20 enregistrements les plus récentes. Mais c'est le genre de chose que j'ai un peu (beaucoup) de mal à implémenter ... Vous n'auriez par un exemple dont je puisse m'inspirer (php 4.1) ? D'un point de vue conceptuel par contre cela me semble plus propre que de passer par une table temporaire ...

[jota5450]

slt.

ton explain veut dire que ton serveur mysql va lire 27*1357*1*1*1*1222 lignes=44.772.858 lignes....


soit t´essaye de diminuir le nº de lignes lus par annu_sites,soit dans la table t.


regarde si tu peut mettre des uniques, ou des primary key sur tes champs.

[fpouget]

Envoyé par jota5450

ton explain veut dire que ton serveur mysql va lire 27*1357*1*1*1*1222 lignes=44.772.858 lignes....

si je met un Union ALL la requete n'est plus obliger de faire la chasse aux doublons, donc si je comprends bien je dois me trouver avec (seulement) 27*1357*1*1*1 lignes=36.639 lignes, non ?

Envoyé par jota5450

soit t´essaye de diminuir le nº de lignes lus par annu_sites,soit dans la table t.

En faisant ça j'ai un bon résultat (0,3 seconde) le pb c'est que ce n'est pas très fiable vu mes données

Envoyé par jota5450

regarde si tu peut mettre des uniques, ou des primary key sur tes champs.

A priori il y a ce qu'il faut ...

[pcaboche]

Envoyé par fpouget

A moins de faire l'insertion dans la table temp en deux temps puis un troisième requête pour faire le order --> cela me semble mieux sachant que je peux mettre un limit dans les deux premiers cela limitera de fait le nb de lignes à traiter.

Je me suis peut-être mal exprimé:
1) tu insères les données du premier SELECT dans la table temp
2) tu insères les données du deuxième SELECT dans la table temp (avec le IGNORE à cause des doublons)
3) tu fais un SELECT * FROM temp ORDER BY ...

Et en effet, tu peux faire un LIMIT lors des insertions

Envoyé par fpouget

Mais passer par une table temporaire ce n'est pas très propre non ?

En entreprise, ça se fait (et si ça marche bien comme ça, tant mieux !). Il faut juste faire attention aux synchronisations (pour pas qu'on ait 2 processus qui écrivent dans la même table...). Comme on dit: "aux grands maux, les grands remèdes !"

Envoyé par fpouget

je stocke les resultats des deux select chacun dans un tableau (en PHP)

Surtout pas! (on ne va pas mettre l'intégralité d'une table en mémoire dans un tableau PHP!)

Ce que je veux dire, c'est que tu prends deux curseurs.

1) Pour chacun d'eux, tu lis le premier élément (mysql_fetch_row)
2) tu compares les deux enregistrements, tu affiches le plus récent
3) tu fais un mysql_fetch_row pour le curseur ayant retourné l'enregistrement que tu viens d'afficher
4) si le curseur en question ne retourne plus de résultat, tu affiches les enregistrements retournés par l'autre curseur, sinon tu retournes en 2

Tu connais la partie "fusion" de l'algorithme de tri fusion? Et bien là, c'est pareil: on a 2 listes triées (nos tables, triés au moyen de nos deux SELECT) que l'on parcours à l'aide de curseurs. Là, on est en train de fusionner le résultat de ces deux listes triées pour en faire une troisième, également triée, qui est la fusion des 2.

Envoyé par fpouget

Vous n'auriez par un exemple dont je puisse m'inspirer (php 4.1) ?

Là? Sous la main? Non, désolé. (mais avec les explications, ça devrait aller, non?)

[fpouget]

Envoyé par pcaboche

Surtout pas! (on ne va pas mettre l'intégralité d'une table en mémoire dans un tableau PHP!)

Je ne comptais pas mettre l'intégalité en mémoire puisque dans ce cas je peux mettre un limit 20 à mes 2 requêtes ...

Envoyé par pcaboche

Ce que je veux dire, c'est que tu prends deux curseurs.

1) Pour chacun d'eux, tu lis le premier élément (mysql_fetch_row)
2) tu compares les deux enregistrements, tu affiches le plus récent
3) tu fais un mysql_fetch_row pour le curseur ayant retourné l'enregistrement que tu viens d'afficher
4) si le curseur en question ne retourne plus de résultat, tu affiches les enregistrements retournés par l'autre curseur, sinon tu retournes en 2

Tu connais la partie "fusion" de l'algorithme de tri fusion? Et bien là, c'est pareil: on a 2 listes triées (nos tables, triés au moyen de nos deux SELECT) que l'on parcours à l'aide de curseurs. Là, on est en train de fusionner le résultat de ces deux listes triées pour en faire une troisième, également triée, qui est la fusion des 2.

La est la limite pour le non developpeur que je suis et qui veux faire du développement ... Mais bon je vais voir ce que je peux trouver.
Rdv la semaine prochaine (parce que là c'est le WE ...)

[pcaboche]

Envoyé par fpouget

La est la limite pour le non developpeur que je suis et qui veux faire du développement ...

Et t'aurais pas un truc pour le développeur qui veut faire du non-développement? (parce que ça va bien 2 minutes les "Désolé monsieur, votre profil n'a pas été retenu car vous n'avez pas d'expérience significative dans le domaine" mais ça commence à être lourd...)

Envoyé par fpouget

Rdv la semaine prochaine (parce que là c'est le WE ...)

Donc si je comprends bien, t'es un non-développeur qui fait du développement et qui est payé pour cela? Comment t'as fait pour être embauché sans expérience dans le domaine? (ça m'intéresse...)

[fpouget]

Envoyé par pcaboche

Donc si je comprends bien, t'es un non-développeur qui fait du développement et qui est payé pour cela? Comment t'as fait pour être embauché sans expérience dans le domaine? (ça m'intéresse...)

Non je ne suis pas payé pour ça, c'est pour mon site perso mais ce WE je vais essayer de prendre un vrai WE et de ne plus y toucher

[pcaboche]

T'as bien raison! Profite de ton week-end.

[jota5450]

sur ta table annu_sites, presque sur que ta pas d´index sur activation.

et si l´explain que ta mis, est avec union all, il lit les lignes que je t´ai dis.

[jota5450]

et sur

Code :

1
2
3
 
 
INNER JOIN forums AS f ON f.id = t.forum_id

essaye

Code :

1
2
3
 
 
INNER JOIN forums AS f  WHERE t.forum_id=f.id

t.forum_id et f.id, index, cle, unique?

[fpouget]

léger gain en indexant activation
par contre pas de changement visible avec

Code :

INNER JOIN forums AS f  WHERE t.forum_id=f.id

f.id = PRIMAIRE
forum_id = Index

[pcaboche]

Envoyé par fpouget

léger gain en indexant activation
par contre pas de changement visible avec

Code :

INNER JOIN forums AS f  WHERE t.forum_id=f.id

Normal: c'est (sensiblement) la même chose.