SQL optimization - 3 tables - Several SUM - 20 thousand records - 12 seconds

Question

I have 3 tables for orders.

1-) orders

orderID ⎜odate ⎜ fullname ⎜address ⎜ ordersum ⎜ status_id

2-) order_products

id ⎜orderID ⎜count⎜ psum ⎜cost

3-) order_sum

orderID ⎜shipping ⎜tax ⎜coupon ⎜ discount

In the order below, I get a "discount / order value / amount of goods / amount of taxes" by date. But the query takes 12 seconds for 20,000 entries. How to optimize it?

SELECT
    DATE(o.odate) AS ODate,
    COUNT(o.orderID) AS OTotal,
    (
        SELECT
            SUM(op.psum)
        FROM
            order_products op
        INNER JOIN orders oo ON oo.orderID = op.orderID
        WHERE
            DATE(oo.odate) = DATE(o.odate)
        AND oo.status_id NOT IN (24, 26, 27, 28, 29)
    ) AS ProductSum,
    SUM(os.shipping) / 118 * 100 AS Shipping,
    SUM(os.tax) AS Tax,
    SUM(o.ordersum) AS allPayments,
    SUM(os.coupon) AS CouponDiscount,
    SUM(os.discount) AS Discount,
    (
        SUM(o.ordersum) / COUNT(DISTINCT o.orderID)
    ) AS BasketAVG
FROM
    orders o
JOIN order_sum os ON os.orderID = o.orderID
WHERE
    o.status_id NOT IN (24, 26, 27, 28, 29)
AND o.odate BETWEEN '2014-12-01'
AND '2014-12-30'
GROUP BY
    DATE(o.odate)
ORDER BY
    o.odate ASC;

Exlpand:

+----+--------------------+-------+--------+-----------------+-----------+---------+-----------------------------+-------+----------------------------------------------+
| id | select_type        | table | type   | possible_keys   | key       | key_len | ref                         | rows  | Extra                                        |
+----+--------------------+-------+--------+-----------------+-----------+---------+-----------------------------+-------+----------------------------------------------+
|  1 | PRIMARY            | o     | ALL    | PRIMARY,orders  | NULL      | NULL    | NULL                        | 10645 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | os    | eq_ref | PRIMARY,ordersum| PRIMARY   | 4       | cikolat_system.o.orderID    |     1 |                                              |
|  2 | DEPENDENT SUBQUERY | oo    | ALL    | PRIMARY,orders  | NULL      | NULL    | NULL                        | 10645 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | op    | ref    | oproducts2      | oproducts2| 5       | cikolat_system.oo.orderID   |     1 | Using where                                  |
+----+--------------------+-------+--------+-----------------+-----------+---------+-----------------------------+-------+----------------------------------------------+

SQLFiddle: http://sqlfiddle.com/#!2/81c921/1

Answer 1

I recommend aligning the correlated subquery in the SELECT list and using the join operation for the inline view. I would write the query as follows:

SELECT DATE(o.odate)                AS ODate
     , COUNT(o.orderID)             AS OTotal
     , ps.ProductSum                AS ProductSum
     , SUM(os.shipping) / 118 * 100 AS Shipping
     , SUM(os.tax)                  AS Tax
     , SUM(o.ordersum)              AS allPayments
     , SUM(os.coupon)               AS CouponDiscount
     , SUM(os.discount)             AS Discount
     , SUM(o.ordersum)
       / COUNT(DISTINCT o.orderID)  AS BasketAVG
  FROM orders o
  JOIN order_sum os
    ON os.orderID = o.orderID
  LEFT
  JOIN ( SELECT op.orderID
              , SUM(op.psum) AS ProductSum
           FROM order_products op
          GROUP BY op.orderID 
       ) ps
    ON ps.orderID = o.orderID
 WHERE o.status_id NOT IN (24, 26, 27, 28, 29)
   AND o.odate BETWEEN '2014-12-01' AND '2014-12-30'
 GROUP BY DATE(o.odate)
 ORDER BY DATE(o.odate) ASC

---

The explanation displays the view; with MySQL 5.5 and earlier versions that will not be indexed.

, orders, , orders MySQL :

... ON orders (odate, status)

, , JOIN . orders , :

  LEFT
  JOIN ( SELECT op.orderID
              , SUM(op.psum) AS ProductSum
           FROM order_products op
           JOIN orders oo
             ON oo.orderID = op.orderID
          WHERE oo.status_id NOT IN (24, 26, 27, 28, 29)
            AND oo.odate BETWEEN '2014-12-01' AND '2014-12-30'
          GROUP BY op.orderID
       ) ps

, order_products MySQL (EXPLAIN " " )

... ON order_products (orderID, psum)

---

( odate DATE, DATE() , MySQL " ". - DATETIME TIMESTAMP, DATE(). , odate , 30- . , 30-.

   AND o.odate >= '2014-12-01 00:00:00'
   AND o.odate <  '2014-12-30 00:00:00'

( , , .)