MySql não está otimizando a consulta corretamente

O otimizador MySQL não pode otimizar expressões neste formato:

WHERE (col_1,col_2) IN ((a,b),(c,d),(e,f))

Não é uma questão de acertar os índices -- parece que não foi implementado.

O otimizador não entende que isso é equivalente a...

WHERE (col_1,col_2) IN ((a,b)) 
   OR (col_1,col_2) IN ((c,d)) 
   OR (col_1,col_2) IN ((e,f)) 

... ou ...

WHERE (col_1 = a AND col_2 = b)
   OR (col_1 = c AND col_2 = d)
   OR (col_1 = e AND col_2 = f)

Existe o Bug #35819 , que encontrei originalmente neste artigo , que por sua vez foi mencionado nos comentários deste post .

Infelizmente, não os encontrei até que já tivesse quebrado o novo Optimizer Trace no MySQL 5.6 e executado alguns casos de teste por meio dele. Parecia uma aposta segura que, se o 5.6 não pudesse lidar com isso, as versões anteriores também não poderiam.

Acontece que o MySQL 5.6 realmente não pode lidar com isso. A construção "conjunto em conjunto de conjuntos" parece simplesmente não ser algo que o otimizador capta. Portanto, neste caso, não é uma questão de o otimizador escolher uma varredura completa da tabela em vez de outros planos -- o otimizador na verdade conclui que não há nenhum outro plano possível a ser considerado.

Isso só é verdade para vários "construtores de linha" no lado direito de IN. Para uma única expressão, o otimizador faz seu trabalho e percebe que isso é equivalente a col_1 = a AND col_2 = b:

WHERE (col_1,col_2) IN ((a,b))    # is optimized correctly
WHERE (col_1,col_2) IN (ROW(a,b)) # is an equivalent expression in MySQL

Curiosamente, seu original EXPLAINsugere que o índice exclusivo não estava sendo usado exatamente da maneira que você pode ter acreditado que estava sendo usado, de qualquer forma. Ele estava sendo usado apenas para encontrar linhas com o sale_id desejado... não ambos os valores.

Você notará em seu original EXPLAINque o key_lené mostrado como 4, o que significa que apenas os 4 bytes mais à esquerda do índice serão examinados -- sale_id, um 4 bytes INTseriam os 4 bytes mais à esquerda nesse índice. Isso significa que o Using whereotimizador percebe que a filtragem adicional das linhas retornadas da varredura de intervalo pode ser necessária para eliminar quaisquer linhas que não satisfaçam o restante da WHEREcláusula -- todas as linhas com sale_id 502 e 504 estão sendo recuperadas por meio do índice, independentemente de seu valor para product_id e, em seguida, as linhas resultantes serão posteriormente filtradas para atender às restrições adicionais impostas pelo WHERE.

O caminho ideal é provavelmente ficar com (expr e expr) ou (expr e expr) ou (expr e expr) em sua cláusula where. É logicamente equivalente e o otimizador o entende.

Nota adicional, em relação a alguns de seus comentários... à luz do que discuti acima, as dicas de índice não ajudarão, porque o otimizador parece não saber da equivalência da expressão que você usou com outras expressões que ele poderia manipular. .. mas como ponto de referência, o motivo pelo qual era sintaticamente inválido é que você precisa usar o nome do índice, não a lista de colunas no índice. Você chamou seu índice exclusivo de 'UNIQUE', então a maneira de usar isso como uma dica de índice seria neste formato:

USE INDEX(`UNIQUE`)

Tente refatorar a consulta como um JOIN total

SELECT B.* FROM
(
    SELECT 502 sale_id,2 product_id
    UNION SELECT 502,1
    UNION SELECT 502,3
    UNION SELECT 504,4
    UNION SELECT 504,2
    UNION SELECT 504,3
) A INNER JOIN sale_product_inventories B
USING (sale_id,product_id);

O índice deve ser utilizado neste caso.

Por que não usou o índice com sua consulta original? Eu culpo a cláusula WHERE porque o Query Optimizer viu a pesquisa sale_id primeiro e provavelmente decidiu que, com o restante da cláusula WHERE, uma verificação completa da tabela era o caminho de menor resistência.

De uma chance !!!