Por que uma condição IN seria mais lenta que “=” no sql?

Verifique a pergunta Esta consulta SELECT leva 180 segundos para concluir (verifique os comentários sobre a questão em si).
A IN chega a ser comparada com apenas um valor, mas ainda assim a diferença de tempo é enorme.
Por que é assim?

Resumo: Este é um problema conhecido no MySQL e foi corrigido no MySQL 5.6.x. O problema é devido a uma otimização ausente quando uma subconsulta usando IN é identificada incorretamente como subconsulta dependente em vez de uma subconsulta independente.


Quando você executa EXPLAIN na consulta original, ele retorna isto:

 1 'PRIMARY' 'question_law_version' 'ALL' '' '' '' '' 10148 'Usando onde'
 2 'SUBQUERIDO DEPENDENTE' 'question_law_version' 'ALL' '' '' '' '' '10148' Using where '
 3 'SUBQUERIDO DEPENDENTE' 'question_law' 'ALL' '' '' '' '' 10040 'Usando onde'

Quando você muda IN para = você recebe isto:

 1 'PRIMARY' 'question_law_version' 'ALL' '' '' '' '' 10148 'Usando onde'
 2 'SUBQUERY' 'question_law_version' 'ALL' '' '' '' '' 10148 'Usando onde'
 3 'SUBQUERY' 'question_law' 'ALL' '' '' '' '' 10040 'Usando onde'

Cada subconsulta dependente é executada uma vez por linha na consulta em que está contida, enquanto a subconsulta é executada apenas uma vez. O MySQL pode às vezes otimizar subconsultas dependentes quando existe uma condição que pode ser convertida em uma junit, mas aqui não é esse o caso.

Agora, isso obviamente deixa a questão de por que o MySQL acredita que a versão IN precisa ser uma subconsulta dependente. Eu fiz uma versão simplificada da consulta para ajudar a investigar isso. Eu criei duas tabelas ‘foo’ e ‘bar’, onde a primeira contém apenas uma coluna id, e a segunda contém um id e um foo id (embora eu não tenha criado uma restrição de chave estrangeira). Então eu preenchi as duas tabelas com 1000 linhas:

 CREATE TABLE foo (id INT PRIMARY KEY NOT NULL); CREATE TABLE bar (id INT PRIMARY KEY, foo_id INT NOT NULL); -- populate tables with 1000 rows in each SELECT id FROM foo WHERE id IN ( SELECT MAX(foo_id) FROM bar ); 

Essa consulta simplificada tem o mesmo problema de antes – a seleção interna é tratada como uma subconsulta dependente e nenhuma otimização é executada, fazendo com que a consulta interna seja executada uma vez por linha. A consulta leva quase um segundo para ser executada. Alterar o IN para = novamente permite que a consulta seja executada quase instantaneamente.

O código que usei para preencher as tabelas está abaixo, caso alguém deseje reproduzir os resultados.

 CREATE TABLE filler ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE=Memory; DELIMITER $$ CREATE PROCEDURE prc_filler(cnt INT) BEGIN DECLARE _cnt INT; SET _cnt = 1; WHILE _cnt <= cnt DO INSERT INTO filler SELECT _cnt; SET _cnt = _cnt + 1; END WHILE; END $$ DELIMITER ; CALL prc_filler(1000); INSERT foo SELECT id FROM filler; INSERT bar SELECT id, id FROM filler; 

É sobre consultas internas, também conhecidas como subqueries vs joins, não sobre IN vs =, e as razões são explicadas nesse post. A versão 5.4 do MySQL é suposta para introduzir um otimizador aprimorado, que pode rewrite algumas subconsultas de forma mais eficiente.

A pior coisa que você pode fazer é usar a assim chamada subconsulta correlacionada http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html

Os otimizadores de SQL nem sempre fazem o que você espera que eles façam. Não tenho certeza se há uma resposta melhor do que isso. É por isso que você precisa examinar a saída do EXPLAIN PLAN e criar um perfil de suas consultas para descobrir onde o tempo é gasto.

É interessante, mas o problema também pode ser resolvido com as declarações preparadas (não tenho certeza se é adequado para todos), por exemplo:

 mysql> EXPLAIN SELECT * FROM words WHERE word IN (SELECT word FROM phrase_words); +----+--------------------+--------------+... | id | select_type | table |... +----+--------------------+--------------+... | 1 | PRIMARY | words |... | 2 | DEPENDENT SUBQUERY | phrase_words |... +----+--------------------+--------------+... mysql> EXPLAIN SELECT * FROM words WHERE word IN ('twist','rollers'); +----+-------------+-------+... | id | select_type | table |... +----+-------------+-------+... | 1 | SIMPLE | words |... +----+-------------+-------+... 

Portanto, apenas prepare a instrução em um procedimento armazenado e execute-a. Aqui está a ideia:

 SET @words = (SELECT GROUP_CONCAT(word SEPARATOR '\',\'') FROM phrase_words); SET @words = CONCAT("'", @words, "'"); SET @query = CONCAT("SELECT * FROM words WHERE word IN (", @words, ");"; PREPARE q FROM @query; EXECUTE q;