Tag: pgsql

Always, ALWAYS use sub-selects!

SQL with sub-sub-sub-selects: 9 minutes

SELECT p.sku AS "Product code",
 p.annotation AS "Product Description",
  (SELECT COUNT(orders_products.fk_product_id) AS "count"
   FROM orders_products
   INNER JOIN orders 
     ON orders.id = orders_products.fk_order_id 
     AND orders.order_created >= '2008-12-01 00:00:00' 
     AND orders.order_created < = '2008-12-07 24:00:00'
   INNER JOIN products 
     ON products.id = orders_products.fk_product_id 
     AND products.sku = p.sku
   WHERE orders_products.fk_order_status_id >= 30) AS "Total number of units sold",
    (
      SELECT SUM((orders_products.price-orders_products.discount)/1.15) AS "sum"
      FROM orders_products
      INNER JOIN orders 
        ON orders.id = orders_products.fk_order_id 
        AND orders.order_created >= '2008-12-01 00:00:00'
        AND orders.order_created < = '2008-12-07 24:00:00'
      INNER JOIN products 
        ON products.id = orders_products.fk_product_id 
        AND products.sku = p.sku
      WHERE orders_products.fk_order_status_id >= 30
     ) AS "Total value"
     FROM products p
      WHERE (
        SELECT COUNT(orders_products.fk_product_id) AS "count"
        FROM orders_products
        INNER JOIN orders 
          ON orders.id = orders_products.fk_order_id 
          AND orders.order_created >= '2008-12-01 00:00:00'
          AND orders.order_created < = '2008-12-07 24:00:00'
        INNER JOIN products 
          ON products.id = orders_products.fk_product_id
          AND products.sku = p.sku
        WHERE orders_products.fk_order_status_id >= 30
      ) > 0

Inner joins for the same thing: 400ms

SELECT products.sku, products.annotation,
  COUNT(orders_products.fk_product_id) AS "count",
  SUM ((orders_products.price-orders_products.discount)/1.15) AS "sum"
  FROM orders_products
  INNER JOIN orders 
    ON orders.id = orders_products.fk_order_id 
    AND orders.order_created >= '2008-12-01 00:00:00' 
    AND orders.order_created < = '2008-12-07 24:00:00'
  INNER JOIN products 
    ON products.id = orders_products.fk_product_id
  WHERE orders_products.fk_order_status_id >= 30
  GROUP BY products.sku, products.annotation