optimiser jointure

Version imprimable

19/02/2006, 01h10
clancy182

optimiser jointure

Bonsoir,
Je dois faire une analyse du traffic du mois de janvier par tranche d'une heure sur les downloads d'un site.
chaque objet en telechargement a un iditifiant id mais certain objets en ont deux , le meme objet est donc telechargeable sous deux identifiants differents.

table objet:
objet id id2
AAA 1 60
BBB 2 NULL
CCC 3 NULL
DDD 4 NULL
EEE 5 61
FFF 6 NULL

mais ts les identifiants sont bien uniques.

La table data contient tous les telechargements qui ont eu lieu.
le champ 'timestamp' marque le debut du telechargement d'un objet et le champ 'url' est l'identifiant de l'objet.

Voila donc ma requete:
#1
SELECT DATE_FORMAT( d.timestamp, '%d %H' ) date, count( * ) y
FROM data d
INNER JOIN objet o ON d.url IN (o.id, o.id2)
WHERE d.timestamp
BETWEEN '2006-01-01 00:00:00' AND '2006-01-31 23:59:59'
GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' )
ORDER BY `date` ASC

Elle marche bien mais elle met bcp de temps à s'executer...
en fait autant de temps qu'une requete faisant un produit cartesien.

#2
SELECT DATE_FORMAT( d.timestamp, '%d %H' ) date, count( * ) y
FROM data d, objet o
WHERE d.url IN (o.id, o.id2)
AND d.timestamp
BETWEEN '2006-01-01 00:00:00' AND '2006-01-31 23:59:59'
GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' )
ORDER BY `date` ASC

le probleme semble venir de la double jointure sur le o.id et o.id2
Si je fais la meme chose que la requete #1 mais avec une simple jointure
INNER JOIN objet o ON d.url = o.id
ou bien INNER JOIN objet o ON d.url o.id2
la requete s'execute relativement rapidement...

si vous voyez des ameliorations possibles, je suis preneur.
Merci d'avance !
19/02/2006, 03h06
pcaboche

Re: optimiser jointure

Extrait de la doc MySQL :

Citation:

expr IN (valeur,...)

Retourne 1 si expr est l'une des valeurs dans la liste IN, sinon retourne 0. Si toutes les valeurs sont des constantes, toutes les valeurs sont évaluées avec le type de expr et triées. La recherche de l'élément est alors faite en utilisant la recherche binaire. Cela signifie que IN est très rapide si les valeurs contenues dans la liste IN sont toutes des constantes.

Sauf que dans ton cas, les valeurs contenues dans la liste ne sont pas des constantes. Essaye ça:

Code:

INNER JOIN objet o ON (d.url=o.id OR d.url=o.id2)

Dans ce cas là, MySQL pourrait éventuellement s'aider des index, ce qui pourrait être un peu plus rapide (avec le IN, c'est impossible).

Le problème de perfs peut aussi venir du GROUP BY. Compare les temps d'exécution avec et sans le GROUP BY pour voir son influence. Dans ce cas, tu peux essayer d'optimiser le GROUP BY: vu que tes téléchargements ont tous lieu le même mois, tu peux faire un GROUP BY sur le jour et l'heure seulement:

Code:

GROUP BY DAY(d.timestamp), HOUR(d.timestamp)

ou

Code:

GROUP BY DATE_FORMAT( d.timestamp, '%d %H')

Compare les temps d'exécution de ces 2 méthodes.

Allez, bonne chance !

Je ne peux pas tester avec mySQL, et cela dépend peut-être de ta version, mais j'essaierais :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT laDate, SUM(y) 
FROM (SELECT DATE_FORMAT( d.timestamp, '%d %H' ) laDate, count( * ) y 
      FROM data d INNER JOIN objet o ON d.url = o.id 
      WHERE d.timestamp BETWEEN '2006-01-01 00:00:00' AND '2006-01-31 23:59:59' 
      GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' ) 
     UNION ALL
      SELECT DATE_FORMAT( d.timestamp, '%d %H' ) laDate, count( * ) y 
      FROM data d INNER JOIN objet o ON d.url = o.id2 
      WHERE d.timestamp BETWEEN '2006-01-01 00:00:00' AND '2006-01-31 23:59:59' 
      GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' ) 
     )
GROUP BY laDate
ORDER BY laDate

Pourquoi GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' ) et non GROUP BY DATE_FORMAT( d.timestamp, '%d %H' ) ?

19/02/2006, 15h35
pcaboche

Citation:

Envoyé par Médiat

Pourquoi GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' ) et non GROUP BY DATE_FORMAT( d.timestamp, '%d %H' ) ?

C'est sympa de me plagier ! :x
19/02/2006, 16h04
Médiat

Citation:

Envoyé par pcaboche

C'est sympa de me plagier ! :x

Parce que tu crois que je te lis ? :lol:
19/02/2006, 16h44
clancy182

Ah ca marche tres bien ca:
INNER JOIN chainestv v ON ( t.url2 = v.sdp OR t.url2 = v.sdp2 )
pourtant j'etais persuadé d'avoir essayer ,peut etre que je l'ai fait sans les parantheses...
les requetes imbriquées ne fonctionnent pas dans ma version.
Merci a vous
19/02/2006, 17h47
pcaboche

Citation:

Envoyé par Médiat

Parce que tu crois que je te lis ? :lol:

Tu devrais: je n'écris pas QUE des conneries. En plus, ça éviterait les rédondances... surtout pour écrire des requêtes compliquées qui ne marchent pas (franchement, tu nous as habitué à mieux. Je suis presque déçu.)
19/02/2006, 18h05
Médiat

Citation:

Envoyé par pcaboche

qui ne marchent pas

Qui marchent (je reprends ton pluriel que je ne m'explique pas) très bien avec une base de données digne de ce nom, pas de ma faute si le posteur initial ne précise pas sa version.

Citation:

Envoyé par pcaboche

Je suis presque déçu.

Pas moi, je te reconnais bien.

Cette fois-ci je ne mets pas le smiley, puisque ton sens de l'humour semble inexistant !
19/02/2006, 18h53
pcaboche

Tu as raison: j'aurais peut-être dû tout mettre au singulier (encore que ce sont des requêtes imbriquées en une seule, pour moi c'est comme si on avait plusieurs requêtes).

Citation:

Envoyé par Médiat

Cette fois-ci je ne mets pas le smiley, puisque ton sens de l'humour semble inexistant !

Ah ok, c'était de l'humour. J'avais pris ça pour une aggression personnelle. Désolé! :calin:
19/02/2006, 19h08
Médiat

Citation:

Envoyé par pcaboche

Ah ok, c'était de l'humour. J'avais pris ça pour une aggression personnelle. Désolé! :calin:

Pas de problème, désolé que tu aies pu mal l'interpréter :calin: .

Revenons à des considérations techniques : le OR et le IN sont tout à faits équivalents, il est donc très étonnant que mySQL sache gérer les index dans un cas et pas dans l'autre, c'est pour cela que j'avais fait une autre proposition dans laquelle j'étais certain que les index seraient utilisés (même s'il y a à la fin un GROUP BY sur des tables temporaires (< 800 lignes c'est négligeables)).
19/02/2006, 19h32
pcaboche

Visiblement, le OR et le IN ne sont pas tout à fait équivalents.

D'après ce qui est écrit dans la doc, si toutes les valeurs du IN sont des constantes, alors MySQL construit un arbre binaire pour accélérer la requête. Dans ce cas, le IN est globalement plus rapide que le OR (recherche binaire plutôt que élément par élément).

Si par contre le IN contient des expressions (ici, des noms de colonnes), celui-ci sera transformé en une succéessions de OR, mais l'expérience montre que les index ne sont pas pris en compte (peut-être que les dernières versions de MySQL font ce type d'optimisation). Dans ce cas, le IN est plus lent que le OR.

En partant de cette hypothèse, je me suis dit qu'on pouvait "faciliter" le travail de MySQL en réécrivant la jointure avec un OR (où là on est sûr qu'il utilisera les index, s'il y en a)
20/02/2006, 00h19
clancy182

en mettant des OR à la place de IN, je passe d'un temps d'execution d'une 10aines de minutes a une 20aines de secondes :)