Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on Oct 26th, 2012  |  syntax: SQL  |  size: 2.15 KB  |  views: 44  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Nu,ber OF ROWS
  2. 235 000 category_product
  3. 58 000 products
  4.  
  5. Relational division:
  6.  
  7. SELECT SQL_NO_CACHE p.*
  8. FROM product_entries p
  9. INNER JOIN
  10. (
  11.     SELECT product_version_id
  12.     FROM category_product_version
  13.     WHERE category_id IN (35, 41)
  14.     GROUP BY product_version_id
  15.     HAVING COUNT(DISTINCT category_id) = 2
  16. ) pc
  17.     ON p.product_version_id = pc.product_version_id
  18.  
  19. 2826 ROWS  ~ 20ms
  20.  
  21. SELECT SQL_NO_CACHE p.*
  22. FROM product_entries p
  23. INNER JOIN
  24. (
  25.     SELECT product_version_id
  26.     FROM category_product_version
  27.     WHERE category_id IN (35,41,706,726,727)
  28.     GROUP BY product_version_id
  29.     HAVING COUNT(DISTINCT category_id) = 5
  30. ) pc
  31.     ON p.product_version_id = pc.product_version_id
  32.  
  33. 46 ROWS ~ 25-30 ms
  34.  
  35. ====
  36. Multiple sub-queries:
  37.  
  38. SELECT SQL_NO_CACHE p.*
  39. FROM product_entries p
  40. WHERE EXISTS
  41. (
  42.     SELECT 1
  43.     FROM category_product_version
  44.     WHERE product_version_id = p.id
  45.     AND category_id = 35
  46. )
  47. AND EXISTS
  48. (
  49.     SELECT 1
  50.     FROM category_product_version
  51.     WHERE product_version_id = p.id
  52.     AND category_id = 41
  53. )
  54.  
  55. 2826 ROWS ~ 5-7ms
  56.  
  57. SELECT SQL_NO_CACHE p.*
  58. FROM product_entries p
  59. WHERE EXISTS
  60. (
  61.     SELECT 1
  62.     FROM category_product_version
  63.     WHERE product_version_id = p.id
  64.     AND category_id = 35
  65. )
  66. AND EXISTS
  67. (
  68.     SELECT 1
  69.     FROM category_product_version
  70.     WHERE product_version_id = p.id
  71.     AND category_id = 41
  72. )
  73. AND EXISTS
  74. (
  75.     SELECT 1
  76.     FROM category_product_version
  77.     WHERE product_version_id = p.id
  78.     AND category_id = 706
  79. )
  80. AND EXISTS
  81. (
  82.     SELECT 1
  83.     FROM category_product_version
  84.     WHERE product_version_id = p.id
  85.     AND category_id = 726
  86. )
  87. AND EXISTS
  88. (
  89.     SELECT 1
  90.     FROM category_product_version
  91.     WHERE product_version_id = p.id
  92.     AND category_id = 727
  93. )
  94.  
  95. 46 ROWS ~ 30 ms
  96.  
  97. One other example:
  98.  
  99. SELECT SQL_NO_CACHE *
  100. FROM product_entries p
  101. WHERE
  102.     (
  103.         SELECT
  104.             COUNT(DISTINCT cp.category_id)
  105.         FROM category_product_version AS cp
  106.         WHERE
  107.             cp.product_version_id = p.product_version_id AND
  108.             cp.category_id IN (35, 41)
  109.     ) = 2
  110.  
  111. .... quit the process BEFORE it could RETURN a RESULT
clone this paste RAW Paste Data