Como o MariaDB Otimiza LEFT JOIN e RIGHT JOIN

A LEFT JOIN B join_condition no MariaDB está implementada como a seguir:

• A tabela B é configurada para ser dependente da tabela A e de todas as tabelas das quais A depende.
• A tabela A é configurada para ser dependente de todas as tabelas (exceto B) que são usadas na condição LEFT JOIN.
• A condição LEFT JOIN é usada para decidir como devemos recuperar registros a partir da tabela B. (Em outras palavras, qualquer condição na claúsula WHERE não é usada).
• Todas as otimizações padrões de join são feitas, com a excessão que uma tabela é sempre lida depois de todas as tabelas das quais é dependente. Se existir uma dependência circular o MariaDB irá emitir um erro.
• Todas as otimizações padrões de WHERE são realizadas.
• Se existir um registro em A que coincida com a cláusula WHERE, mas não existir nenhum registro em B que coincida com a condição ON então um registro extra em B é gerado com todas as colunas com valor NULL.
• Se você utiliza LEFT JOIN para encontrar registros que não existem em alguma tabela e está usando o seguinte teste: nome_coluna IS NULL na parte WHERE, onde nome_colun é um campo que é declarado como NOT NULL, então o MariaDB para de pesquisar por mais registros (para uma combinação particular de chaves) depois de ter encontrado um registro que combinar com a condição LEFT JOIN.

RIGHT JOIN é implementado de forma análoga à LEFT JOIN.

A ordem de leitura das tabelas forçada por LEFT JOIN e STRAIGHT JOIN irá ajudar o otimizador de joins (que calcula em qual ordem as tabelas devem ser unidas) a fazer seu trabalho mais rapidamente, já que haverão poucas permutações de tabelas a serem conferidas.

Perceba que o texto acima significa que se você fizer uma consulta do tipo:

SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key)
 WHERE b.key=d.key

A partir do MariaDB 4.0.14, o MariaDB faz a seguinte otimização LEFT JOIN:

Se a condição WHERE é sempre falsa para a linha NULL gerada, o LEFT JOIN é alterado para um join normal.

Por exemplo, na seguinte consulta a cláusula WHERE seria falso se t2.coluna fosse NULL, asssim é seguro converter para uma join normal.

SELECT * FROM t1 LEFT t2 ON (column) WHERE t2.column2 =5;
->
SELECT * FROM t1,t2 WHERE t2.column2=5 AND t1.column=t2.column;

Isto pode ser feito mais rápido já que o MariaDB pode agora usar a tabela t2 antes da tabela t1 se resultasse consulta melhor. Para forçar uma ordem de tabela específica, use STRAIGHT JOIN.

O MariaDB irá fazer uma pesquisa completa em b já que o LEFT JOIN irá força-lo a ser lido antes de d.

A correção neste caso é alterar a consulta para:

Retornar