Cláusula SQL IN más lenta que las consultas individuales

Estoy usando la implementación JPA de Hibernate con MySQL 5.0.67. MySQL está configurado para usar InnoDB.

Al realizar una consulta JPA (que se traduce a SQL), descubrí que usar la cláusula IN es más lento que realizar consultas individuales. Ejemplo:

 SELECT p FROM Person p WHERE p.name IN ('Joe', 'Jane', 'Bob', 'Alice') 

es más lento que cuatro consultas separadas:

 SELECT p FROM Person p WHERE p.name = 'Joe' SELECT p FROM Person p WHERE p.name = 'Jane' SELECT p FROM Person p WHERE p.name = 'Bob' SELECT p FROM Person p WHERE p.name = 'Alice' 

¿Por qué es esto? ¿Es esto una limitación de performance MySQL?

Esta es una deficiencia conocida en MySQL.

A menudo es cierto que el uso de UNION funciona mejor que una consulta de range como la que muestra. MySQL no emplea índices muy inteligentemente para expresiones que usan IN (...) . Existe un agujero similar en el optimizador para expresiones booleanas con OR .

Consulte http://www.mysqlperformanceblog.com/2006/08/10/using-union-to-implement-loose-index-scan-to-mysql/ para get una explicación y puntos de reference detallados.

El optimizador se está mejorando todo el time. Una deficiencia en una versión de MySQL puede mejorarse en una versión posterior. Por lo tanto, vale la pena probar sus consultas en diferentes versiones.

También es ventajoso usar UNION ALL lugar de simplemente UNION . Ambas consultas usan una tabla temporal para almacenar resultados, pero la diferencia es que UNION aplica DISTINCT al set de resultados, que incurre en una sorting no indexada adicional.

Si está utilizando el operador IN, no es muy diferente de decir:

 (p.name = 'Joe' OR p.name = 'Jane' OR p.name = 'Bob' OR p.name = 'Alice') 

Esas son cuatro condiciones que deben verificarse para cada fila que la consulta debe considerar. Por supuesto, cada otra consulta que cites tiene una sola condición. No creo en la mayoría de los escenarios del mundo real, ya que hacer cuatro de estas consultas sería más rápido, ya que debe considerar el time que le toma a su cliente leer los sets de resultados y hacer algo con ellos. En ese caso, IN se ve muy bien; aún mejor si puede usar un índice.

Una consulta tan simple como la IN demostrada no debería tener un problema con el optimizador que elige usar el índice. El trabajo de UNIÓN mencionado por Bill solo se requiere ocasionalmente cuando tiene consultas más complejas. Podría ser un problema con las statistics del índice.

¿Has hecho ANALIZAR sobre la table en cuestión?

¿Cuántas filas hay en la tabla y cuántas coinciden con la cláusula IN?

¿Qué dice EXPLAIN para las consultas en cuestión?

¿Estás midiendo el time del reloj de panetworking o el time de ejecución de la consulta? Supongo que el time de ejecución real para cada una de las cuatro consultas individuales puede sumr less que el time para ejecutar la consulta IN, pero el time total del reloj de panetworking será mucho más largo para las cuatro consultas.

Ayudará a tener un índice en la columna de nombre.

Para mí, porque la cláusula IN puede liberar la database y las tablas para ser utilizadas por otras conexiones, y hay una ventaja de la estructura de la aplicación al usarla, la cláusula IN es una herramienta invaluable, incluso si hay un ligero retraso en las consultas individuales.

La siguiente técnica se utiliza en casi todas las aplicaciones PHP / MySQL que construyo.

Uso la cláusula IN bastante con las teclas numéricas:

p.ej

tomar cinco elementos maestros y todos los secundarios pueden ser:

 $master_arr = mysql_query( select * from master table where master_id in (1,7,9,10) ); 

entonces:

 $subitem_arr = mysql_query( select * from subitems table where par_master_id in (1,7,9,10) ); 

añada el subcampo a los elementos maestros:

 foreach($subitem_arr AS $sv){ $m_key = $sv['par_master_id']; $s_key = $sv['subitem_id']; $master_arr[$m_key]['subitem'][$s_key] = $sv; } 

Esto hace dos cosas: 1.) las tablas no se mantienen todas juntas al unir 2.) solo dos consultas mysql producen un tree de datos

puede hacer que la cláusula in sea más rápida si obtiene primero los valores y luego incrusta los valores en la cláusula in en lugar de incrustar la consulta sql en la sentencia sql

aquí hay un ejemplo de uso en la cláusula