code_junkie

Practical limit to length of SQL query (specifically MySQL)

Nov 11th, 2011
116
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.38 KB | None | 0 0
  1. 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 Consumable' AND type!='Companion' AND type!='Mount' AND (type!='Armor' OR (false )) AND (type!='Weapon' OR (false )) ORDER BY type ASC, itemlevel ASC, name ASC
  2.  
  3. select *
  4. from
  5. 4e_magic_items mi
  6. ,4e_magic_item_levels mil
  7. ,4e_monster_sources ms
  8. where mi.id = mil.itemid
  9. and mi.source = ms.id
  10. and itemlevel between 1 and 30
  11. and source not in(16,2,5,13,15,3,4,12,7,14,11,10,8,1,6,9)
  12. and type not in(
  13. 'Arms' ,'Feet' ,'Hands' ,'Head' ,'Neck' ,'Orb' ,
  14. 'Potion' ,'Ring' ,'Rod' ,'Staff' ,'Symbol' ,'Waist' ,
  15. 'Wand' ,'Wondrous Item' ,'Alchemical Item' ,'Elixir' ,
  16. 'Reagent' ,'Whetstone' ,'Other Consumable' ,'Companion' ,
  17. 'Mount'
  18. )
  19. and ((type != 'Armor') or (false))
  20. and ((type != 'Weapon') or (false))
  21. order by
  22. type asc
  23. ,itemlevel asc
  24. ,name asc
  25.  
  26. /*
  27. Some thoughts:
  28. ==============
  29. 0 - Formatting really matters, in SQL even more than most languages.
  30. 1 - consider selecting only the columns you need, not "*"
  31. 2 - use of table aliases makes it short & clear ("MI", "MIL" in my example)
  32. 3 - joins in the WHERE clause will un-clutter your FROM clause
  33. 4 - use NOT IN for long lists
  34. 5 - logically, the last two lines can be added to the "type not in" section.
  35. I'm not sure why you have the "or false", but I'll assume some good reason
  36. and leave them here.
  37. */
  38.  
  39. SELECT x, y, z
  40. FROM a, b
  41. WHERE fiz = 1
  42. AND foo = 2
  43. AND a.x = b.y
  44. AND b.z IN (SELECT q, r, s, t
  45. FROM c, d, e
  46. WHERE c.q = d.r
  47. AND d.s = e.t
  48. AND c.gar IS NOT NULL)
  49. ORDER BY b.gonk
Add Comment
Please, Sign In to add comment