fechas - like sql

¿Por qué una condición IN sería más lenta que "=" en sql?

Question

Verifique la pregunta Esta consulta SELECT tarda 180 segundos en finalizar (verifique los comentarios sobre la pregunta en sí).
El IN se compara con un solo valor, pero la diferencia de tiempo es enorme.
¿Por qué es así?

Answer 1

Es interesante, pero el problema también se puede resolver con las declaraciones preparadas (no estoy seguro de si es adecuado para todos), por ejemplo:

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 |...
+----+-------------+-------+...

Así que solo prepare la declaración en un procedimiento almacenado, luego ejecútelo. Aquí está la idea:

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;

Answer 2

Los optimizadores de SQL no siempre hacen lo que usted espera que hagan. No estoy seguro de que haya una mejor respuesta que eso. Es por eso que tiene que examinar la salida del PLAN EXPLAIN, y el perfil de sus consultas para saber dónde se gasta el tiempo.

Answer 3

Resumen: Este es un problema conocido en MySQL y fue corregido en MySQL 5.6.x. El problema se debe a una optimización faltante cuando una subconsulta que utiliza IN se identifica incorrectamente como subconsulta dependiente en lugar de una subconsulta independiente.

Cuando ejecuta EXPLAIN en la consulta original, devuelve esto:

1 ''PRIMARY'' ''question_law_version'' ''ALL'' '''' '''' '''' '''' 10148 ''Using where'' 2 ''DEPENDENT SUBQUERY'' ''question_law_version'' ''ALL'' '''' '''' '''' '''' 10148 ''Using where'' 3 ''DEPENDENT SUBQUERY'' ''question_law'' ''ALL'' '''' '''' '''' '''' 10040 ''Using where''

Cuando cambias IN a = obtienes esto:

1 ''PRIMARY'' ''question_law_version'' ''ALL'' '''' '''' '''' '''' 10148 ''Using where'' 2 ''SUBQUERY'' ''question_law_version'' ''ALL'' '''' '''' '''' '''' 10148 ''Using where'' 3 ''SUBQUERY'' ''question_law'' ''ALL'' '''' '''' '''' '''' 10040 ''Using where''

Cada subconsulta dependiente se ejecuta una vez por fila en la consulta en la que está contenida, mientras que la subconsulta se ejecuta solo una vez. MySQL a veces puede optimizar subconsultas dependientes cuando hay una condición que se puede convertir a una unión, pero aquí no es el caso.

Ahora, por supuesto, esto deja la pregunta de por qué MySQL cree que la versión IN debe ser una subconsulta dependiente. He hecho una versión simplificada de la consulta para ayudar a investigar esto. Creé dos tablas ''foo'' y ''bar'' donde el primero contiene solo una columna de identificación, y el último contiene tanto un id como un foo id (aunque no creé una restricción de clave externa). Luego llené ambas tablas con 1000 filas:

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
);

Esta consulta simplificada tiene el mismo problema que antes: la selección interna se trata como una subconsulta dependiente y no se realiza ninguna optimización, lo que hace que la consulta interna se ejecute una vez por fila. La consulta tarda casi un segundo en ejecutarse. Cambiar el IN a = nuevamente permite que la consulta se ejecute casi al instante.

El código que utilicé para completar las tablas está debajo, en caso de que alguien desee reproducir los 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;

Answer 4

Se trata de consultas internas, también conocidas como subconsultas y combinaciones, no sobre IN vs =, y las razones se explican en esa publicación. Se necesita la versión 5.4 de MySQL para introducir un optimizador mejorado que pueda reescribir algunas subconsultas en una forma más eficiente.

Lo peor que puede hacer es usar la denominada subconsulta correlacionada http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html