Négocier avec une grosse jointure MySQL

Récemment, j’ai ajouté un antispam sur murl.fr. Celui-ci est assez rudimentaire et se base sur un blacklistage de domaines (ou sous-domaines).
Ainsi, je peux filtrer les urls à leur création, ainsi qu’à leur affichage.

Sur murl, il existe une page qui liste les derniers liens créés et le « top 10″ (de clics) de la journée. Les requêtes qui chargent les liens sur cette pages ont donc été modifiées pour intégrer la blacklist.

Voici une de ces requêtes :

SELECT lu.id, SUM( ls.clics ) AS cliclic
    FROM lil_stats ls
    INNER JOIN lil_urls lu
        ON ls.id = lu.id
    LEFT JOIN lil_domain_blacklist ldb1
        ON (ldb1.domain = lu.domain OR ldb1.domain = lu.subdomain)
        AND ldb1.b_display =1
WHERE ls.date="2010-10-31"
    AND ldb1.id IS NULL
GROUP BY lu.id
ORDER BY cliclic DESC
LIMIT 0 , 10

Cette requête fonctionnait très bien quand la table lil_domain_blacklist était petite (au début, elle faisait moins de 100 lignes…)

Puis, j’ai importé un énorme fichier de domaines reconnus comme spammeurs. Ce fichier comportait environ 150000 entrées, et là, ma requête ne passait plus et finissait par bloquer le serveur.

En faisant un explain de cette requête, on remarque que la table lil_domain_blacklist est analysée en entier, et qu’elle ne fait appel à aucun index…

J’ai donc décidé de changer les choses (en même temps, je n’avais pas trop le choix…), en séparant la jointure sur la table incriminée en deux jointures.

Voici la nouvelle requête :

SELECT lu.id, SUM( ls.clics ) AS cliclic
    FROM lil_stats ls
    INNER JOIN lil_urls lu
        ON ls.id = lu.id
    LEFT JOIN lil_domain_blacklist ldb1
        ON ldb1.domain = lu.domain
        AND ldb1.b_display =1
    LEFT JOIN lil_domain_blacklist ldb2
        ON ldb2.domain = lu.subdomain
        AND ldb2.b_display =1
WHERE ls.date="2010-10-31"
    AND ldb1.id IS NULL
    AND ldb2.id IS NULL
GROUP BY lu.id
ORDER BY cliclic DESC
LIMIT 0 , 10

Cette nouvelle version de la requête s’exécute en environ 0.025 secondes, ce qui est tout à fait correct.

En observant un explain de celle-ci, on constate que les 2 jointures sur la table lil_domain_blacklist utilisent correctement les index et retournent un nombre limité de lignes.

Ainsi, je sais désormais qu’il vaut mieux, parfois, faire 2 jointures sur une même table qu’une seule jointure sur cette même table.

Et toi, ami lecteur, as-tu déjà fait face à ce cas de figure ?




Les commentaires sont fermés.