Si vous avez déjà été amené à manipuler de grosses requêtes avec des LEFT JOIN en cascade qui comportent de multiples conditions "or" dans ces jointures, cet article devrait vous intéresser.

Imaginons 3 tables:

• news avec FK nullable vers user + FK nullable vers account
• user avec FK nullable vers account
• account

Bien entendu, vous souhaitez récupérer en une seule requête l'ensemble des news avec optionnellement le user et/ou l'accout.

Au 1er abord, vous allez tenter un:

SELECT
    *
FROM
    news n
    LEFT JOIN user u ON n.user_id = u.user_id
    LEFT JOIN account a ON u.account_id = a.account_id

Ce qui fonctionnera mais ne vous donnera que toutes les news + user (si y'a jointure) + account (si y'a jointure à partir du user).

Sauf que notre table news a également une FK nullable vers account, le problème est donc que vous ne récupérés pas les accounts des news qui n'ont pas de users, puisque la jointure d'account se fait avec user.

En gros, votre requête donne:

news -> user (si il y en a) -> account (si il y a un user et qu'il a une FK vers account)

Mais pas:

news -> account (si la news a directement une FK vers account)

Vous pouvez changer la jointure mais le problème sera inversé, vous n'aurez que les accounts des news directes mais plus les accounts des users des news.

Au 2ème abord, vous allez tenter d'ajouter une condition de jointure "or" sur account :

SELECT
    *
FROM
    news n
    LEFT JOIN user u ON n.user_id = u.user_id
    LEFT JOIN account a ON (u.account_id = a.account_id OR n.account_id = a.account_id)

Ce qui permet de joindre l'account à partir du user OU à partir de la news.

Là, vous vous dites « Victoire ! » mais comment réagit MySQL lorsqu'on lui propose plusieurs possibilités de jointure ?

La raison (tout du moins la mienne) voudrait que l'on se dise qu'il essaye la 1ère condition (u.account_id = a.account_id) en se basant sur ses index (PK et FK), puis la 2ème (n.account_id = a.account_id) en se basant encore sur ses index, etc.

Hélas la réalité est toute autre : ne sachant pas sur quelle condition et donc quel index se baser, MySQL va réaliser des full table scan entre news et user PUIS entre news et account.

Un EXPLAIN vous donnera ce genre de message qui ne font pas plaisir du tout :

Type: ALL + Extra: Range checked for each record (index map: 0x1)

Je vous laisse imaginer ce que cela peut donner si vous avez 5 ou 6 conditions "or" de jointure, le tout dans des sous-requêtes avec group by, having, fonctions mathématiques et autres traitements merveilleux.

On arrive très rapidement à des millions de lignes scannés et des temps d'éxecution exponentiels.

Au 3e abord, on se dit que si MySQL est incapable de trouver la condition adéquate, on va lui forcer la main :

Ce n'est pas très sexy mais parfaitement fonctionnel. On remplace les "or" par des "if" imbriqués afin de faire le boulot à sa place et de lui fournir directement la condition et donc les index à utiliser.

SELECT
    *
FROM
    news n
    LEFT JOIN user u ON n.user_id = u.user_id
    LEFT JOIN account a ON (IF (u.account_id IS NOT NULL, u.account_id, n.account_id) = a.account_id)

Nous passons de full table scans à un simple matching par index.

Dans mon cas présent, je suis passé de plus 5 millions de lignes scannées à seulement 25 500 lignes, merci MySQL !

Plus d'infos sur le site de MySQL: http://dev.mysql.com/doc/refman/5.0/en/explain-output.html