Límite práctico de la longitud de la consulta SQL (específicamente MySQL)

¿Es particularmente malo tener una consulta SQL muy grande con muchas cláusulas WHERE (potencialmente networkingundantes)?

Por ejemplo, aquí hay una consulta que he generado desde mi aplicación web con todo apagado, que debería ser la mayor consulta posible para que este progtwig genere:

SELECT * FROM 4e_magic_items INNER JOIN 4e_magic_item_levels ON 4e_magic_items.id = 4e_magic_item_levels.itemid INNER JOIN 4e_monster_sources ON 4e_magic_items.source = 4e_monster_sources.id WHERE (itemlevel BETWEEN 1 AND 30) AND source!=16 AND source!=2 AND source!=5 AND source!=13 AND source!=15 AND source!=3 AND source!=4 AND source!=12 AND source!=7 AND source!=14 AND source!=11 AND source!=10 AND source!=8 AND source!=1 AND source!=6 AND source!=9 AND type!='Arms' AND type!='Feet' AND type!='Hands' AND type!='Head' AND type!='Neck' AND type!='Orb' AND type!='Potion' AND type!='Ring' AND type!='Rod' AND type!='Staff' AND type!='Symbol' AND type!='Waist' AND type!='Wand' AND type!='Wondrous Item' AND type!='Alchemical Item' AND type!='Elixir' AND type!='Reagent' AND type!='Whetstone' AND type!='Other Consumble' AND type!='Companion' AND type!='Mount' AND (type!='Armor' OR (false )) AND (type!='Weapon' OR (false )) ORDER BY type ASC, itemlevel ASC, name ASC 

Parece funcionar bastante bien, pero tampoco es un tráfico particularmente intenso (unos cientos de visitas al día más o less), y me pregunto si valdría la pena intentar optimizar las consultas para eliminar networkingundancias y cosas así.

Leer su consulta me hace querer jugar un juego de rol.

Esto definitivamente no es demasiado largo. Siempre y cuando estén bien formateados, diría que un límite práctico es de aproximadamente 100 líneas. Después de eso, será mejor que rompas las subconsultas en vistas solo para evitar que tus ojos se crucen.

He trabajado con algunas consultas de más de 1000 líneas, y eso es difícil de depurar.

Por cierto, ¿puedo sugerir una versión reformateada? Esto es principalmente para demostrar la importancia del formateo; Confío en que esto será más fácil de entender.

 select * from 4e_magic_items mi ,4e_magic_item_levels mil ,4e_monster_sources ms where mi.id = mil.itemid and mi.source = ms.id and itemlevel between 1 and 30 and source not in(16,2,5,13,15,3,4,12,7,14,11,10,8,1,6,9) and type not in( 'Arms' ,'Feet' ,'Hands' ,'Head' ,'Neck' ,'Orb' , 'Potion' ,'Ring' ,'Rod' ,'Staff' ,'Symbol' ,'Waist' , 'Wand' ,'Wondrous Item' ,'Alchemical Item' ,'Elixir' , 'Reagent' ,'Whetstone' ,'Other Consumble' ,'Companion' , 'Mount' ) and ((type != 'Armor') or (false)) and ((type != 'Weapon') or (false)) order by type asc ,itemlevel asc ,name asc /* Some thoughts: ============== 0 - Formatting really matters, in SQL even more than most languages. 1 - consider selecting only the columns you need, not "*" 2 - use of table aliases makes it short & clear ("MI", "MIL" in my example) 3 - joins in the WHERE clause will un-clutter your FROM clause 4 - use NOT IN for long lists 5 - logically, the last two lines can be added to the "type not in" section. I'm not sure why you have the "or false", but I'll assume some good reason and leave them here. */ 

La limitación pnetworkingeterminada del server MySQL 5.0 es " 1MB ", configurable hasta 1GB.

Esto se configura a través de la configuration max_allowed_packet tanto en el cliente como en el server, y la limitación efectiva es el arrendador de los dos.

Advertencias:

  • Es probable que esta limitación de "package" no se asigne directamente a los caracteres en una statement de SQL. Seguramente desea tener en count la encoding de caracteres dentro del cliente, algunos metadatos de packages, etc.)

SELECCIONE @@ global.max_allowed_packet

este es el único límite real que se puede ajustar en un server, por lo que no hay una respuesta real

Desde una perspectiva práctica, generalmente considero que cualquier SELECCIÓN que termine tomando más de 10 líneas para escribir (poniendo cada cláusula / condición en una línea separada) es demasiado larga para mantenerla fácilmente. En este punto, probablemente debería hacerse como un procedimiento almacenado de algún tipo, o debería tratar de encontrar una forma mejor de express el mismo concepto, posiblemente creando una tabla intermedia para capturar alguna relación que, con frecuencia, estoy consultando.

Su kilometraje puede variar, y hay algunas consultas excepcionalmente largas que tienen una buena razón para estar. Pero mi regla de oro es 10 líneas.

Ejemplo (SQL medianamente incorrecto):

 SELECT x, y, z FROM a, b WHERE fiz = 1 AND foo = 2 AND ax = by AND bz IN (SELECT q, r, s, t FROM c, d, e WHERE cq = dr AND ds = et AND c.gar IS NOT NULL) ORDER BY b.gonk 

Esto es probablemente demasiado grande; la optimization, sin embargo, dependería en gran medida del context.

Solo recuerde, cuanto más larga y compleja sea la consulta, más difícil será mantenerla.

La mayoría de las bases de datos admiten procedimientos almacenados para evitar este problema. Si su código es lo suficientemente rápido como para ejecutarse y es fácil de leer, no desea tener que cambiarlo para disminuir el time de compilation.

Una alternativa es usar declaraciones preparadas para que solo reciba el hit una vez por cada connection del cliente y luego solo ingrese los parameters para cada llamada.

¿Supongo que quieres decir con "apagado" que un campo no tiene un valor?

En lugar de verificar si algo no es así, y tampoco es eso, ¿no puede simplemente verificar si el campo es nulo? O establezca el campo en 'apagado' y verifique si tipo o lo que sea igual a 'apagado'.