EN vs O en la cláusula WHERE de SQL
Cuando se trata de grandes bases de datos, que funciona mejor, IN o OR en la cláusula SQL Where -?
¿Hay alguna diferencia en la forma en que son ejecutados?
6 answers
Asumo que quieres saber la diferencia de rendimiento entre lo siguiente:
WHERE foo IN ('a', 'b', 'c')
WHERE foo = 'a' OR foo = 'b' OR foo = 'c'
De acuerdo con el manual para MySQL si los valores son constantes IN ordena la lista y luego usa una búsqueda binaria. Me imagino que OR los evalúa uno por uno sin ningún orden en particular. Así que IN es más rápido en algunas circunstancias.
La mejor manera de saber es perfilar ambos en su base de datos con sus datos específicos para ver cuál es más rápido.
Probé ambos en un MySQL con 1000000 filas. Cuando la columna está indexada no hay diferencia perceptible en el rendimiento, ambos son casi instantáneos. Cuando la columna no está indexada obtuve estos resultados:
SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);
1 row fetched in 0.0032 (1.2679 seconds)
SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)
Así que en este caso el método que usa OR es aproximadamente un 30% más lento. Agregar más términos hace que la diferencia sea mayor. Los resultados pueden variar en otras bases de datos y en otros datos.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-06-19 08:17:20
La mejor manera de averiguarlo es mirando el Plan de Ejecución.
Lo probé con Oracle, y fue exactamente lo mismo.
CREATE TABLE performance_test AS ( SELECT * FROM dba_objects );
SELECT * FROM performance_test
WHERE object_name IN ('DBMS_STANDARD', 'DBMS_REGISTRY', 'DBMS_LOB' );
Aunque la consulta usa IN, el Plan de Ejecución dice que usa OR:
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 163 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| PERFORMANCE_TEST | 8 | 1416 | 163 (2)| 00:00:02 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBMS_LOB' OR "OBJECT_NAME"='DBMS_REGISTRY' OR
"OBJECT_NAME"='DBMS_STANDARD')
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-06-19 07:39:56
Creo que Oracle es lo suficientemente inteligente como para convertir el menos eficiente uno (cualquiera que sea) en el otro. Así que creo que la respuesta debería depender más bien de la legibilidad de cada uno (donde creo que IN claramente gana)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-06-19 07:27:32
El operador OR necesita un proceso de evaluación mucho más complejo que el constructo IN porque permite muchas condiciones, no solo iguales como IN.
Aquí hay un tipo de lo que se puede utilizar con O pero que no son compatibles con EN: mayor. mayor o igual, menos, menos o igual, COMO y algunos más como el oráculo REGEXP_LIKE. Además, tenga en cuenta que las condiciones no siempre pueden comparar el mismo valor.
Para el optimizador de consultas es más fácil administrar el operador IN porque es solo una construcción que define el operador OR en múltiples condiciones con el operador = en el mismo valor. Si utiliza el operador OR, el optimizador puede no considerar que siempre está utilizando el operador = en el mismo valor y, si no realiza una elaboración más profunda y mucho más compleja, probablemente podría excluir que solo haya operadores = para los mismos valores en todas las condiciones involucradas, con la consiguiente exclusión de métodos de búsqueda optimizados como el ya mencionado búsqueda binaria.
[EDITAR] Probablemente un optimizador no puede implementar optimizado EN el proceso de evaluación, pero esto no excluye que una vez podría suceder(con una actualización de la versión de la base de datos). Por lo tanto, si utiliza el operador OR, esa elaboración optimizada no se utilizará en su caso.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-06-20 10:00:46
OR tiene sentido (desde el punto de vista de la legibilidad), cuando hay menos valores para comparar.
IN es útil esp. cuando tiene una fuente dinámica, con la que desea que se comparen los valores.
Otra alternativa es usar un JOIN con una tabla temporal.
No creo que el rendimiento deba ser un problema, siempre que tenga los índices necesarios.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-06-19 07:22:08
Hice una consulta SQL en un gran número de OR (350). Postgres do it 437.80 ms.
Ahora usar EN:
23.18 ms
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-12-21 09:40:42