how to optimize big result to sum in MySQL 5.7

by Dolphin   Last Updated June 30, 2020 03:06 AM

Now I have a user consume history record table, sometimes the data is very big(>1million) per day.Now I have to calculate the table to get report data,this is my sql:

    SELECT SUM(consume_num) AS consumeNum, SUM(transin_num) AS transinNum
    , CASE 
        WHEN COUNT(DISTINCT transin_user_num) - 1 < 0 THEN 0
        ELSE COUNT(DISTINCT transin_user_num) - 1
    END AS transinUserNum, SUM(transin_count) AS transinCount, SUM(transout_num) AS transoutNum
    , CASE 
        WHEN COUNT(DISTINCT transout_user_num) - 1 < 0 THEN 0
        ELSE COUNT(DISTINCT transout_user_num) - 1
    END AS transoutUserNum, SUM(transout_count) AS transoutCount, SUM(send_amount) AS sendAmount
    , CASE 
        WHEN COUNT(DISTINCT sender_num) - 1 < 0 THEN 0
        ELSE COUNT(DISTINCT sender_num) - 1
    END AS senderNum, SUM(send_num) AS sendNum, SUM(picked_amount) AS pickedAmount
    , CASE 
        WHEN COUNT(DISTINCT picker_num) - 1 < 0 THEN 0
        ELSE COUNT(DISTINCT picker_num) - 1
    END AS pickerNum, SUM(picked_num) AS pickedNum, SUM(bumped_amount) AS bumpedAmount
    , CASE 
        WHEN COUNT(DISTINCT bumper_num) - 1 < 0 THEN 0
        ELSE COUNT(DISTINCT bumper_num) - 1
    END AS bumperNum, SUM(bumped_num) AS bumpedNum, SUM(back_money_amount) AS backMoneyAmount
FROM (
    SELECT CASE 
            WHEN consume_item IN (
                'SEND_RED_ENVELOPE', 
                'BUMPED_SELF', 
                'LOCKED', 
                'HAPPY_RAIN_SEND', 
                'HAPPY_CARD_MATCH', 
                'HAPPY_RAIN_BUMPED_SELF', 
                'HAPPY_BOMB_SETTLEMENT_UNLOCK'
            ) THEN ABS(consume_num)
            ELSE 0
        END AS consume_num
        , CASE 
            WHEN consume_item IN ('PLATFORM_IN') THEN ABS(consume_num)
            ELSE 0
        END AS transin_num
        , CASE 
            WHEN consume_item IN ('PLATFORM_IN') THEN user_id
            ELSE -1
        END AS transin_user_num
        , CASE 
            WHEN consume_item IN ('PLATFORM_IN') THEN 1
            ELSE 0
        END AS transin_count
        , CASE 
            WHEN consume_item IN ('PLATFORM_OUT') THEN ABS(consume_num)
            ELSE 0
        END AS transout_num
        , CASE 
            WHEN consume_item IN ('PLATFORM_OUT') THEN user_id
            ELSE -1
        END AS transout_user_num
        , CASE 
            WHEN consume_item IN ('PLATFORM_OUT') THEN 1
            ELSE 0
        END AS transout_count
        , CASE 
            WHEN consume_item IN ('SEND_RED_ENVELOPE', 'HAPPY_RAIN_SEND') THEN ABS(consume_num)
            ELSE 0
        END AS send_amount
        , CASE 
            WHEN consume_item IN ('SEND_RED_ENVELOPE', 'HAPPY_RAIN_SEND') THEN user_id
            ELSE -1
        END AS sender_num
        , CASE 
            WHEN consume_item IN ('SEND_RED_ENVELOPE', 'HAPPY_RAIN_SEND') THEN 1
            ELSE 0
        END AS send_num
        , CASE 
            WHEN consume_item IN ('PICK_RED_ENVELOPE', 'HAPPY_RAIN_PICK') THEN ABS(consume_num)
            ELSE 0
        END AS picked_amount
        , CASE 
            WHEN consume_item IN ('PICK_RED_ENVELOPE', 'HAPPY_RAIN_PICK') THEN user_id
            ELSE -1
        END AS picker_num
        , CASE 
            WHEN consume_item IN ('PICK_RED_ENVELOPE', 'HAPPY_RAIN_PICK') THEN 1
            ELSE 0
        END AS picked_num
        , CASE 
            WHEN consume_item IN ('BUMPED_SELF', 'HAPPY_RAIN_BUMPED_SELF') THEN ABS(consume_num)
            ELSE 0
        END AS bumped_amount
        , CASE 
            WHEN consume_item IN ('BUMPED_SELF', 'HAPPY_RAIN_BUMPED_SELF') THEN user_id
            ELSE -1
        END AS bumper_num
        , CASE 
            WHEN consume_item IN ('BUMPED_SELF', 'HAPPY_RAIN_BUMPED_SELF') THEN 1
            ELSE 0
        END AS bumped_num
        , CASE 
            WHEN consume_item IN (
                'RED_ENVELOPE_BACK', 
                'PICK_RED_ENVELOPE', 
                'BUMPED_OTHERS', 
                'TICK_WIN', 
                'REVOLVER_ABANDON_WIN', 
                'REVOLVER_GET_SHOT_WIN', 
                'REVOLVER_NOT_GET_SHOT_WIN', 
                'UNLOCKED', 
                'HAPPY_RAIN_BACK', 
                'HAPPY_CARD_SCRAPE'
            ) THEN ABS(consume_num)
            ELSE 0
        END AS back_money_amount
    FROM wallet_consume_record
    WHERE created_time < 1593359999999
        AND created_time >= 1593273600000
) a;

and I have already create index in the created_time columns.This is the explain output:

+------+---------------+-----------------------+--------------+--------+---------------------------------+--------+-----------+--------+----------+------------+-------------+
|   id | select_type   | table                 |   partitions | type   | possible_keys                   |    key |   key_len |    ref |     rows |   filtered | Extra       |
|------+---------------+-----------------------+--------------+--------+---------------------------------+--------+-----------+--------+----------+------------+-------------|
|    1 | SIMPLE        | wallet_consume_record |       <null> | ALL    | wallet_consume_created_time_idx | <null> |    <null> | <null> | 15624863 |         23 | Using where |
+------+---------------+-----------------------+--------------+--------+---------------------------------+--------+-----------+--------+----------+------------+-------------+
1 row in set
Time: 0.031s

this is the dataset result:

mysql [email protected]:meow_report_pro> select count(*)
                                     -> from wallet_consume_record
                                     -> where created_time < 1593359999999
                                     -> AND created_time >= 1593273600000;
+------------+
|   count(*) |
|------------|
|    1876234 |
+------------+
1 row in set
Time: 0.403s

now the sql need 30s+ to complete execute and always timeout in my application query. is it possible to make the sql better to get high performance?

Tags : mysql


Related Questions


The equivalent of CTE in MySQL from Microsoft SQL

Updated November 15, 2016 08:02 AM


getting innodb buffer usage

Updated August 24, 2018 15:06 PM