not - mysql update in

Número de elementos MySQL dentro de "cláusula in"

Question

Tengo tres tablas para definir a los usuarios:

USER: user_id (int), username (varchar)
USER_METADATA_FIELD: user_metadata_field_id (int), field_name (varchar)
USER_METADATA: user_metadata_field_id (int), user_id (int), field_value (varchar)

Me gustaría crear un usuario de nivel medio que tenga cierto acceso a otros usuarios dentro de la aplicación. Para determinar a qué usuarios puede acceder el uso de sesión, estoy utilizando una subconsulta como la siguiente:

SELECT user_id FROM user WHERE user_id 
     IN (SELECT user_id 
         FROM user_metadata 
         WHERE user_metadata_field_id = 1 AND field_value = ''foo'')

Actualmente, estoy almacenando la secuencia de subconsulta en una variable y luego la inserto dinámicamente en la consulta externa cada vez que necesito obtener una lista de usuarios. Después de hacer esto, pensé: "tiene que ser mejor almacenar una cadena de los user_id s reales".

Entonces, en lugar de almacenar esto en una variable ...

$subSql = "SELECT user_id FROM user_metadata WHERE user_metadata_field_id = 1 AND field_value = ''foo''";

... De hecho, realizo la consulta y almaceno el resultado así ...

$subSql = "12, 56, 89, 100, 1234, 890";

Luego, cuando necesito atraer a un litro de usuarios a los que el usuario conectado tiene acceso, puedo hacerlo con:

$sql = "SELECT user_id FROM user WHERE user_id IN ($subSql)";

Y finalmente las preguntas:

¿Cuántos elementos puede usar en una CLASE IN MySQL IN ? Almacenar los identificadores reales en lugar de la declaración sub-sql tiene que ser más rápido para realizar esa consulta externa cada vez, ¿verdad?

Answer 1

A partir de un cierto número, las tablas IN son más rápidas.

MySQL tiene algo dentro de su código que hace que construir un rango sobre un gran número de valores constantes sea más lento que hacer lo mismo en un bucle anidado.

Vea este artículo en mi blog para detalles de rendimiento:

• Pasar parámetros en MySQL: lista IN vs. tabla temporal

Answer 2

Como se insinuó en la respuesta de Quassnoi, uno tropieza con otras consideraciones prácticas, antes de llegar a cualquier posible límite impuesto por la implementación de una versión de MySQL dada (*). Por lo tanto, a medida que crece el número de usuarios administradores (u otros criterios que pueden requerir una construcción IN), se debe buscar el uso de alternativas a una "IN" literal, como el uso de tablas temporales (o incluso permanentes).

Dado que está considerando un manejo especial de los criterios del "usuario administrador", por motivos de rendimiento, me gustaría ofrecer un comentario y una sugerencia.

Comentario: ¿Podría ser este un caso de optimización prematura?
No conozco los detalles de esta base de datos, su volumen, complejidad, etc. Y, sí, conozco el tributo por rendimiento que se debe pagar al formato EAV (Entity-Attribute-Value), pero estoy pensando que incluso para negocios exitosos, la base de datos de cuentas raramente cuenta más de 10,000 usuarios. Entonces, incluso con muchos atributos por usuario, seguimos viendo una tabla EAV relativamente pequeña, que puede no requerir este tipo de optimización. (Por otro lado, algunos otros trucos de optimización pueden ser bienvenidos en otras áreas).
Además, los casos de uso típicos implican pocas consultas relativas a la base de datos de la cuenta, en relación con otras consultas, y esta es, por lo tanto, otra razón para rechazar cualquier consideración de rendimiento no trivial para las características relacionadas con las cuentas de la aplicación.

Sugerencia: tal vez usar "atributos re-normalizados"
Para atributos que son de valor único, y en particular si son cortos, se pueden mover (o duplicar) en la tabla Entidad (tabla ''USUARIO'' en este caso). Esto introduce un poco de lógica en el momento en que se insertan o actualizan los elementos, pero esto requiere muchas combinaciones (o subconsultas) y también brinda oportunidades para considerar índices de campos múltiples para admitir los casos de uso más comunes.

(*) ¿Hay un limt?
No he leído sobre tal límite; Sé que Oracle ha tenido un límite de 1,000 en algún momento, MSSQL no; por supuesto, todos los servidores tienen un límite basado en la longitud total de la declaración SQL, ¡pero este es un número realmente grande! si alguna vez tropieza con eso, él / ella tiene otros problemas ... ;-)

Answer 3

Del manual :

La cantidad de valores en la lista IN solo está limitada por el valor de max_allowed_packet .

Answer 4

La cláusula IN de MySQL en sí no tiene ese límite. Intenté con 8000 elementos, funciona bien para mí. El error de desbordamiento de pila podría ser de declaración variable,