Teoria dos índices para consulta com junções

Um erro comum é

SELECT ... SUM(a.foo) ...
    FROM a
    JOIN b ON ...
    WHERE ...
    GROUP BY a.id

Quando você tem JOINduas tabelas, geralmente obtém uma explosão do número de linhas. O SUMé então feito, provavelmente resumindo a.foo repetidamente, levando à resposta 'errada' (grande demais).

Mas não foi isso que você perguntou.

A JOINé quase sempre executado desta forma :

1. Escolha uma mesa para começar. Aplique qualquer filtragem ( WHERE). Até agora, aplicam-se as regras de tabela única.
2. Passe para outra mesa. Mas desta vez, use "Nested Loop Join". Isso significa "para cada linha da etapa 1, alcance a tabela 2".

Portanto, a nova pergunta que você está fazendo é como acessar a tabela 2 ? Existe uma ONcláusula (supondo que não seja uma "junção cruzada"). Isso fornece um (ou mais) campo que deve ser usado no 'melhor' índice da tabela 2. Em seguida, use esse campo, mais quaisquer outros campos em WHERE, juntamente com as regras de tabela única para construir o índice ideal para esse JOIN.

Uma vez que o otimizador pode escolher uma ordem diferente de tabelas ou pode ter várias opções, encontre os melhores índices para cada ordem.

Então simplifique .

• INDEX(x), INDEX(x,y)-->INDEX(x,y)
• Não tenha índices com mais de, digamos, 5 campos.
• Se você estiver ingressando na PRIMARY KEYtabela 2, não precisará de um índice mais longo.
• Considere "cobrir" índices. ( EXPLAINdirá "Usando índice", não deve ser confundido com "Usando condição de índice", que é ICP.)

Seguem algumas dicas de como o otimizador pensa ao unir duas tabelas:

• Uma mesa comWHERE filtro será escolhida antes de uma sem.
• Outras coisas sendo 'iguais', a mesa menor será escolhida primeiro.
• Às vezes, an ORDER BYserá preferido a a WHEREna ordenação das tabelas.
• LIMITraramente é um fator, embora provavelmente devesse ser.

Meu livro de receitas também se concentra em mesa única; talvez esta resposta me ajude a escrever sobre JOINs.