Número de itens do MySQL dentro de “cláusula in”

Eu tenho três tabelas para definir usuários:

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) 

Gostaria de criar um usuário de nível intermediário que tenha certo access a outros usuários no aplicativo. Para determinar quais usuários o uso conectado pode acessar, estou usando uma subconsulta como a seguinte:

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

Atualmente, estou armazenando a sequência de subconsultas em uma variável e inserindo-a dinamicamente na consulta externa sempre que preciso puxar uma lista de usuários. Depois de fazer isso, pensei: “É melhor apenas armazenar uma string dos user_id s reais”.

Então, ao invés de armazenar isso em uma variável …

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

… Eu realmente executo a consulta e armazeno o resultado assim …

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

Então quando eu precisar puxar um lit de usuários que o usuário logado tem access, eu posso fazer isso com:

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

E finalmente as perguntas:

Quantos itens você pode usar em um MySQL IN CLAUSE? Armazenar os IDs reais em vez da instrução sub-sql precisa ser mais rápido para executar essa consulta externa a cada vez, certo?

A partir de um certo número, as tabelas IN são mais rápidas.

MySQL tem algo dentro de seu código que faz com que construir um intervalo sobre um grande número de valores constantes seja mais lento do que fazer o mesmo em um loop nested.

Veja este artigo no meu blog para detalhes de desempenho:

  • Passando parâmetros no MySQL: lista IN vs. tabela temporária

Do manual :

O número de valores na lista IN é limitado apenas pelo valor max_allowed_packet .

Como sugerido na resposta de Quassnoi, a pessoa se depara com outras considerações práticas, antes de atingir qualquer limite possível imposto pela implementação de uma determinada versão do MySQL (*). Portanto, à medida que o número de usuários admin (ou outros critérios que podem requerer uma construção IN) cresce, deve-se procurar usar alternativas a um literal “IN”, como o uso de tabelas temporárias (ou mesmo permanentes).

Como você está considerando o tratamento especial dos critérios “usuário administrador”, para fins de desempenho, eu gostaria de oferecer um comentário e uma sugestão.

Comentário: este poderia ser um caso de otimização prematura?
Eu não tenho conhecimento das especificidades deste database, seu volume, complexidade, etc. E, sim, estou ciente de alguns o tributo de desempenho a ser pago para o formato EAV (Entity-Attribute-Value), mas estou pensando que mesmo para empresas de sucesso, o database de contas raramente conta com mais de 10.000 usuários. Assim, mesmo com muitos atributos por usuário, ainda estamos olhando para uma tabela EAV relativamente pequena, o que pode não exigir esse tipo de otimização. (Por outro lado, alguns outros truques de otimização podem ser bem-vindos em outras áreas).
Além disso, os casos de uso típicos envolvem um número relativamente pequeno de consultas no database da conta, em relação a outras consultas, e isso é outro motivo para diferenciar qualquer consideração de desempenho não trivial para os resources relacionados a contas do aplicativo.

Sugestão: Talvez use “atributos re-normalizados”
Para atributos que são de valor único e, em particular, se forem curtos, eles podem ser movidos (ou duplicados) na tabela Entidade (tabela ‘USER’ neste caso). Isso introduz um pouco de lógica no momento em que os itens são inseridos ou atualizados, mas isso dá nome a muitas junções (ou subconsultas) e também fornece oportunidades para considerar índices de vários campos para suportar os casos de uso mais comuns.

(*) Existe um limt?
Eu não li sobre tal limite; Eu sei que o Oracle tem um limite de 1.000 em algum momento, o MSSQL não; É claro que todos os servidores têm um limite baseado no comprimento total da instrução SQL, mas esse é um número realmente grande! se alguém se deparar com aquele, ele / ela tem outros problemas … 😉

A Cláusula IN do MySQL em si não tem esse limite. Eu tentei com 8000 elementos o seu trabalho bem para mim. Erro de estouro de pilha pode ser de variável declarada,