MySQL optimize huge query

Question

I have really big SQL query:

SELECT
    partner_id,
    the_date,
    SUM(clicks) as clicks,
    SUM(total_count) as total_count,
    SUM(count) as count,
    SUM(total_sum) as total_sum,
    SUM(received_sum) as received_sum,
    SUM(partner_fee) as partner_fee
FROM (
    SELECT
        clicks.partner_id,
        clicks.click_date as the_date,
        clicks,
        orders.total_count,
        orders.count,
        orders.total_sum,
        orders.received_sum,
        orders.partner_fee
    FROM
        (SELECT
            partner_id, click_date, sum(clicks) as clicks
        FROM
            daily_metric WHERE DATE(click_date) BETWEEN '2013-04-01' AND '2013-04-30'
        GROUP BY partner_id , click_date) as clicks
        LEFT JOIN
        (SELECT
            partner_id,
                DATE(order_date) as order_dates,
                SUM(order_sum) as total_sum,
                SUM(customer_paid_sum) as received_sum,
                SUM(partner_fee) as partner_fee,
                count(*) as total_count,
                count(CASE
                    WHEN status = 1 THEN 1
                    ELSE NULL
                END) as count
        FROM
            transaction WHERE DATE(order_date) BETWEEN '2013-04-01' AND '2013-04-30'
        GROUP BY DATE(order_date) , partner_id) as orders ON orders.partner_id = clicks.partner_id AND clicks.click_date = orders.order_dates
    UNION ALL SELECT
        orders.partner_id,
        orders.order_dates as the_date,
        clicks,
        orders.total_count,
        orders.count,
        orders.total_sum,
        orders.received_sum,
        orders.partner_fee
    FROM
        (SELECT
            partner_id, click_date, sum(clicks) as clicks
        FROM
            daily_metric  WHERE DATE(click_date) BETWEEN '2013-04-01' AND '2013-04-30'
        GROUP BY partner_id , click_date) as clicks
            RIGHT JOIN
        (SELECT
            partner_id,
                DATE(order_date) as order_dates,
                SUM(order_sum) as total_sum,
                SUM(customer_paid_sum) as received_sum,
                SUM(partner_fee) as partner_fee,
                count(*) as total_count,
                count(CASE
                    WHEN status = 1 THEN 1
                    ELSE NULL
                END) as count
        FROM
            transaction  WHERE DATE(order_date) BETWEEN '2013-04-01' AND '2013-04-30'
        GROUP BY DATE(order_date) , partner_id) as orders ON orders.partner_id = clicks.partner_id AND clicks.click_date = orders.order_dates
    WHERE
        clicks.partner_id is NULL
    ORDER BY the_date DESC
    ) as t
    GROUP BY the_date ORDER BY the_date DESC LIMIT 50 OFFSET 0

This is explain of my query:

+----+--------------+--------------+--------+---------------+---------+---------+------+---------+----------------------------------------------+
| id | select_type  | table        | type   | possible_keys | key     | key_len | ref  | rows    | Extra                                        |
+----+--------------+--------------+--------+---------------+---------+---------+------+---------+----------------------------------------------+
|  1 | PRIMARY      | <derived2>   | ALL    | NULL          | NULL    | NULL    | NULL |     162 | Using temporary; Using filesort              |
|  2 | DERIVED      | NULL         | NULL   | NULL          | NULL    | NULL    | NULL |    NULL | no matching row in const table               |
|  4 | DERIVED      | transaction  | ALL    | NULL          | NULL    | NULL    | NULL |  280118 | Using where; Using temporary; Using filesort |
|  3 | DERIVED      | daily_metric | index  | NULL          | PRIMARY | 1541    | NULL | 9370157 | Using where                                  |
|  5 | UNION        | <derived6>   | system | NULL          | NULL    | NULL    | NULL |       0 | const row not found                          |
|  5 | UNION        | <derived7>   | ALL    | NULL          | NULL    | NULL    | NULL |     162 |                                              |
|  7 | DERIVED      | transaction  | ALL    | NULL          | NULL    | NULL    | NULL |  280118 | Using where; Using temporary; Using filesort |
|  6 | DERIVED      | daily_metric | index  | NULL          | PRIMARY | 1541    | NULL | 9370157 | Using where                                  |
| NULL | UNION RESULT | <union2,5>   | ALL    | NULL          | NULL    | NULL    | NULL |    NULL | Using filesort                               |
+----+--------------+--------------+--------+---------------+---------+---------+------+---------+----------------------------------------------+
9 rows in set (12.92 sec)

I need any proposition how to optimize this query to > 5s

Tables indexes:

mysql> show index from transaction
    -> ;
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| transaction |          0 | PRIMARY    |            1 | id          | A         |      279478 |     NULL | NULL   |      | BTREE      |         |               |
| transaction |          1 | partner_id |            1 | partner_id  | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| transaction |          1 | updated_at |            1 | updated_at  | A         |         495 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

mysql> show index from daily_metric;
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| daily_metric |          0 | PRIMARY           |            1 | partner_id  | A         |          19 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          0 | PRIMARY           |            2 | click_date  | A         |       10776 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          0 | PRIMARY           |            3 | utm_content | A         |      700476 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          0 | PRIMARY           |            4 | utm_term    | A         |     9806670 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          1 | partner_id_index  |            1 | partner_id  | A         |          19 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          1 | utm_content_index |            1 | utm_content | A         |      891515 |     NULL | NULL   |      | BTREE      |         |               |
| daily_metric |          1 | utm_term_index    |            1 | utm_term    | A         |     9806670 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.03 sec)

Table daily_metric have ~10mln records.

won't make a difference until you remove the DATE() function from the order_date column. — Geoduck
– Geoduck, Commented Sep 9, 2014 at 8:00

GolezTrol · Accepted Answer · 2014-09-09 07:57:12Z

2

You filter by (where clause) transaction.order_date. I don't see index for that fields. Adding it should already make a difference.

Also, click_date is used for filtering, but it's in a separate index (PK) with partner_id. Since you don't filter by partner_id, you would benefit from having a separate index on click_date as well.

answered Sep 9, 2014 at 7:57

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Geoduck · Accepted Answer · 2014-09-09 07:55:10Z

2

You need to break this down to all the subqueries and figure out where the most time is spent. Then you could see if an index would help. Try timing the subqueries by themselves.

One thing I see...you have an "ORDER BY the_date" in the second half of the UNION ALL query, but then you do an "ORDER BY the_date" AGAIN after they are unionized. You only need the 2nd one.

Another thing...you have no indexes on the DATE fields, but that is your main filter on all of these. If you indexed on 'click_date', for example, it won't use the index if your WHERE uses "DATE(click_date)". It is better to do something like this:

WHERE click_date BETWEEN '2013-04-01 00:00:00' AND '2013-04-30 23:59:59'

and have an index on click_date. Then it can find those relevant records faster.

answered Sep 9, 2014 at 7:55

Geoduck

9,0212 gold badges29 silver badges27 bronze badges

4 Comments

Geoduck Over a year ago

I just saw your indexes. You don't have one on "Order_Date", which you use. And, you need to rewrite your WHERE so that the click_date index will be used (don't use a function on the column, which makes index unusable). also, not sure why so many indexes are PRIMARY. Is it possible TWO daily_metrics have the exact same click_date? better to be a regular KEY.

Paul Maxwell Over a year ago

agree, don't use function on data, but avoid BETWEEN for date ranges too, WHERE click_date >= '2013-04-01' and click_date < '2013-05-01' is easier and safer (time precision is smaller than one second)

Geoduck Over a year ago

mysql has no precision less than 1 sec, but I agree that might be easier.

Paul Maxwell Over a year ago

MySQL 5.6.4 and up expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision

dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html using 23:59:59 as a date range boundary is just bad I'm afraid

Collectives™ on Stack Overflow

MySQL optimize huge query

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related