Ordem do índice composto

Para esta consulta

SELECT *
FROM table_a
    JOIN table_b USING (id)
WHERE table_b.column = 1

A maneira ideal é executá-lo é

1. A WHEREcláusula fornece alguma filtragem, então vamos fazer uso dela. Ou seja, tenha um índice table_b começando com column . (Mais tarde, discutiremos se devemos torná-lo composto.) Assim, o Otimizador usará esse índice para encontrar linha(s) de table_b.
2. Para cada uma dessas linhas, JOINpara table_a. (Observe que JOIN, not LEFT JOINestá sendo usado; LEFT JOINé uma história diferente.)
3. Para acessar table_a, é necessário um índice começando com id. (Nota: USING(id)significa table_a.id = table_b.id.)

Até agora, temos

b:  INDEX(column)
a:  INDEX(id)   -- though it probably exists as PRIMARY KEY(id)

Cobertura?

Não sabemos quais outras colunas existem nas duas tabelas. Se houver muito poucas colunas, pode ser tentador criar um índice de "cobertura". Este é um índice que contém todas as colunas necessárias em qualquer lugar do arquivo SELECT. O benefício é alguma aceleração de desempenho ao olhar apenas no BTree do índice e não ter que tocar no BTree de dados.

Pois table_b, seria tentador dizer INDEX(column, id). Isso seria bom (e 'cobrindo') se houvesse apenas essas duas colunas. Mas provavelmente há mais colunas. Então, provavelmente INDEX(column)é tudo o que vale a pena fazer.

Para table_a, suponho que idseja o PRIMARY KEY(que é, por definição, único e um índice). Então, nada mais é necessário lá.

Bottom line : Use os dois índices de coluna única listados acima.

E este exemplo não exemplifica nada sobre índices "compostos". Para saber mais sobre isso, veja

Cardinalidade e intervalo
Cardinalidade e índice composto
de uma coluna Livro de
receitas de indexação

mas muitas vezes eu adiciono um índice composto com ambos que podem melhorar a eficiência de consultas como esta ...

Melhor exemplo

Como eu disse, seu exemplo não exemplifica a pergunta. Então, vou tentar responder "Quando devo usar um índice composto"? Existem muitos casos (veja os links); Vou lhe dar um caso simples.

WHERE x = 1
  AND y > 2

As características relevantes são:

• xe yestão na mesma tabela. (Não é possível criar um índice em duas tabelas.)
• ANDé usado. ( ORnão pode ser otimizado.)
• Um dos testes é com =. (Composto não ajudará se ambos forem intervalos.)
• yé um "intervalo" (exemplos: y>2, y LIKE 'm%', y BETWEEN ... AND ...).

A regra geral é :

1. Coloque todas as =colunas primeiro ( xno meu exemplo)
2. Coloque uma coluna de intervalo por último ( y)

Ou seja, você deve encomendá-lo INDEX(x,y).

Para WHERE x = 1 AND y = 2(ambos =), não importa se você tem INDEX(x,y)ou INDEX(y,x).

Outro detalhe : Com ENGINE=InnoDB, as PRIMARY KEYcolunas são implicitamente anexadas a cada chave secundária. Portanto, seu INDEX(column)é o mesmo que INDEX(column, id). Mas este fato não desempenha um papel nesta discussão.

Percebo que estou discordando de outras respostas aqui (e em outros lugares), mas mantenho minha posição.

Uma boa regra geral é tornar a coluna inicial em um índice composto o mais seletivo possível. Uma boa maneira de imaginar isso é com a analogia da lista telefônica: imagine que você precisa encontrar alguém na lista telefônica e há dois índices... o primeiro é Sobrenome, Nome. O segundo é FirstName, LastName. Qual índice você usaria para encontrar alguém chamado John Xilofone? Certamente você usaria o índice LastName, Firstname, pois há muito poucas entradas de Xilofone e levará muito menos tempo do que procurar em todas as entradas de John uma com o sobrenome de Xilofone.

Portanto, se idfor altamente seletivo e columntiver baixa seletividade, você deseja que o índice seja (id, column), mas se columntiver alta seletividade e idbaixa seletividade, provavelmente se beneficiará de ter o índice definido como (column, id).

Você pode ver um índice (column, id)sendo usado se estiver unindo duas tabelas quando resultar idem um número substancialmente reduzido de linhas que precisam ser unidas.where column = xx

Na consulta acima, existe uma regra rígida e rápida que eu possa seguir para saber qual ordem de chave funcionará melhor?

No exemplo que você deu, sua melhor aposta é não ter um índice composto se você estiver livre para alterar a ordem de junção:

create table table_a(id integer, dummy_a integer);
create index index_a on table_a(id);
create table table_b(id integer, col integer, dummy_b integer);
create index index_b on table_b(col);

explain select * from table_b join table_a using(id) where table_b.col=1;

identificação | select_type | mesa | divisórias | tipo | chaves_possíveis | chave | key_len | ref | linhas | filtrado | Extra      
-: | :---------- | :------ | :------------- | :--- | :------------ | :------ | :------ | :-------------------------- | ---: | -------: | :----------
 1 | SIMPLES | tabela_b | nulo        | ref | index_b | index_b | 5 | const | 1 | 100,00 | Usando onde
 1 | SIMPLES | tabela_a | nulo        | ref | index_a | index_a | 5 | fiddle_YRFDITQONPXNRMDBQSYV.table_b.id | 1 | 100,00 | nulo       

db<>fique aqui