CODEWITHSUNDEEP
mysql
jia Jimmy
jia Jimmy

Reputation: 1848

How can I speed up the sql query?

Say there are two tables like:

Table campaign:


+-------------------+--------------+------+-----+-------------------+-----------------------------+
| Field             | Type         | Null | Key | Default           | Extra                       |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| id                | int(11)      | NO   | PRI | NULL              | auto_increment              |
| campaign_id       | varchar(64)  | YES  | UNI | NULL              |                             |
| account_id        | varchar(64)  | YES  | MUL | NULL              |                             |
| name              | blob         | YES  |     | NULL              |                             |
| objective         | varchar(64)  | YES  |     | NULL              |                             |
| can_use_spend_cap | tinyint(2)   | YES  |     | NULL              |                             |
| status            | varchar(64)  | YES  |     | NULL              |                             |
| spend_cap         | bigint(14)   | YES  |     | 0                 |                             |
| effective_status  | char(128)    | YES  |     | NULL              |                             |
| promoted_object   | text         | YES  |     | NULL              |                             |
| lifetime_budget   | bigint(14)   | YES  |     | NULL              |                             |
| daily_budget      | bigint(14)   | YES  |     | NULL              |                             |
| update_time       | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| create_time       | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |                             |
| task_status       | tinyint(3)   | YES  |     | NULL              |                             |
| is_smb            | tinyint(3)   | YES  |     | NULL              |                             |
| rule_created      | tinyint(4)   | YES  |     | 0                 |                             |
| access_token      | varchar(255) | YES  | MUL | NULL              |                             |
+-------------------+--------------+------+-----+-------------------+-----------------------------+

And table campaign_statistic

+---------------------------+---------------+------+-----+---------+----------------+
| Field                     | Type          | Null | Key | Default | Extra          |
+---------------------------+---------------+------+-----+---------+----------------+
| id                        | int(11)       | NO   | PRI | NULL    | auto_increment |
| campaign_id               | varchar(64)   | YES  | MUL | NULL    |                |
| account_id                | varchar(64)   | YES  |     | NULL    |                |
| impressions               | int(11)       | YES  |     | NULL    |                |
| clicks                    | int(11)       | YES  |     | NULL    |                |
| spend                     | decimal(14,6) | YES  |     | NULL    |                |
| cpc                       | decimal(14,6) | YES  |     | NULL    |                |
| cpm                       | decimal(14,6) | YES  |     | NULL    |                |
| cpp                       | decimal(14,6) | YES  |     | NULL    |                |
| ctr                       | decimal(14,6) | YES  |     | NULL    |                |
| cost_per_purchase         | decimal(14,6) | YES  |     | NULL    |                |
| cost_per_addtocart        | decimal(14,6) | YES  |     | NULL    |                |
| fb_pixel_addtocart        | int(11)       | YES  |     | NULL    |                |
| fb_pixel_addtocart_values | decimal(14,6) | YES  |     | NULL    |                |
| fb_pixel_purchase         | int(11)       | YES  |     | NULL    |                |
| fb_pixel_purchase_values  | decimal(14,6) | YES  |     | NULL    |                |
| roas                      | decimal(14,6) | YES  |     | NULL    |                |
| roi                       | decimal(14,6) | YES  |     | NULL    |                |
| dt                        | date          | YES  |     | NULL    |                |
+---------------------------+---------------+------+-----+---------+----------------+

And I want to join these two tables then do a order, search and time_range work.

I've tried with my poor attempt like this:

EXPLAIN SELECT
    campaign.id AS id,
    campaign.campaign_id AS campaign_id,
    campaign.account_id AS account_id,
    campaign.daily_budget AS daily_budget,
    campaign.lifetime_budget AS lifetime_budget,
    campaign.name AS name,
    campaign.effective_status AS effective_status,
    campaign.daily_budget AS daily_budget,
    statistic.spend AS spend,
    statistic.clicks AS clicks,
    statistic.impressions AS impressions,
    statistic.spend AS spend,
    statistic.fb_pixel_addtocart AS fb_pixel_addtocart,
    statistic.fb_pixel_addtocart_values AS fb_pixel_addtocart_values,
    statistic.fb_pixel_purchase AS fb_pixel_purchase,
    statistic.fb_pixel_purchase_values AS fb_pixel_purchase_values,

    statistic.cpc AS cpc,
    statistic.ctr AS ctr,
    statistic.cpm AS cpm,
    statistic.cost_per_addtocart AS cost_per_addtocart,
    statistic.cost_per_purchase AS cost_per_purchase,
    statistic.roi AS roi,
    statistic.roas AS roas,
    campaign.rule_created AS rule_created,
    campaign.is_smb AS is_smb

    FROM `campaign` as campaign

    LEFT JOIN

    (SELECT

    campaign_id,
    SUM(impressions) AS impressions,
    SUM(clicks) AS clicks,
    ROUND(SUM(spend),2) AS spend,
    SUM(fb_pixel_addtocart) AS fb_pixel_addtocart,
    ROUND(SUM(fb_pixel_addtocart_values),2) AS fb_pixel_addtocart_values,
    ROUND(SUM(fb_pixel_purchase),2) AS fb_pixel_purchase,
    ROUND(SUM(fb_pixel_purchase_values),2) AS fb_pixel_purchase_values,

    ROUND(SUM(clicks)*100/SUM(impressions), 6) AS ctr,
    ROUND(SUM(spend)/SUM(clicks), 6) AS cpc,
    ROUND(SUM(spend)*1000/SUM(impressions), 6) AS cpm,

    ROUND(SUM( spend )/SUM(fb_pixel_addtocart), 6) AS cost_per_addtocart,
    ROUND(SUM( spend )/SUM(fb_pixel_purchase), 6) AS cost_per_purchase,

    ROUND(SUM( fb_pixel_purchase )/SUM(spend), 6) AS roi,
    ROUND(SUM( fb_pixel_purchase )/SUM(spend), 6) AS roas

    FROM `campaign_statistic` WHERE  dt BETWEEN 2019-07-08 AND 2019-07-08  GROUP BY  campaign_id) statistic

    ON statistic.campaign_id = campaign.campaign_id

    WHERE  campaign.account_id = 1000623940119431

    AND (campaign.campaign_id LIKE "%%" OR campaign.name LIKE "%%") ORDER BY id ASC

It works but query was much slow, sorry that I'm new with mysql.

How can I optimize the sql and speed up the query?

Any commentary is very welcome. great thanks.



update question:

When use I do calculate work usingSUM,ROUND or other function. It seems that

index wasn't used and query speed was more low. Why does this happen?


1)

EXPLAIN 

SELECT  campaign_id

FROM `campaign_statistic` 

WHERE  dt >= '2019-03-01' AND dt <= '2019-03-31'  

GROUP BY  fb_campaign_id



+----+-------------+----------------------------+-------+--------------------+---------------+---------+------+--------+-----------------------------------------------------------+
| id | select_type | table                      | type  | possible_keys      | key           | key_len | ref  | rows   | Extra                                                     |
+----+-------------+----------------------------+-------+--------------------+---------------+---------+------+--------+-----------------------------------------------------------+
|  1 | SIMPLE      | campaign_statistic         | range | campaign_date,date | campaign_date | 4       | NULL | 567748 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+----------------------------+-------+--------------------+---------------+---------+------+--------+-----------------------------------------------------------+


2) 

EXPLAIN 

SELECT campaign_id,
SUM(impressions) AS impressions

FROM `campaign_statistic` 

WHERE  dt >= '2019-03-01' AND dt <= '2019-03-31'  

GROUP BY  fb_campaign_id



+----+-------------+----------------------------+------+--------------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table                      | type | possible_keys      | key  | key_len | ref  | rows    | Extra                                        |
+----+-------------+----------------------------+------+--------------------+------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | campaign_statistic         | ALL  | campaign_date,date | NULL | NULL    | NULL | 1647182 | Using where; Using temporary; Using filesort |
+----+-------------+----------------------------+------+--------------------+------+---------+------+---------+----------------------------------------------+

Upvotes: 1

Views: 94

Answers (2)

Jigar Gajjar
Jigar Gajjar

Reputation: 333

Can you avoid aggregation operations and do it in your language program?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271023

First, your query has some obvious flaws. It should look like:

SELECT . . . 
FROM `campaign` as campaign LEFT JOIN
      (SELECT campaign_id,
              SUM(impressions) AS impressions,
              SUM(clicks) AS clicks,
              ROUND(SUM(spend),2) AS spend,
              SUM(fb_pixel_addtocart) AS fb_pixel_addtocart,
              ROUND(SUM(fb_pixel_addtocart_values),2) AS fb_pixel_addtocart_values,
              ROUND(SUM(fb_pixel_purchase),2) AS fb_pixel_purchase,
              ROUND(SUM(fb_pixel_purchase_values),2) AS fb_pixel_purchase_values,

              ROUND(SUM(clicks)*100/SUM(impressions), 6) AS ctr,
              ROUND(SUM(spend)/SUM(clicks), 6) AS cpc,
              ROUND(SUM(spend)*1000/SUM(impressions), 6) AS cpm,

              ROUND(SUM( spend )/SUM(fb_pixel_addtocart), 6) AS cost_per_addtocart,
              ROUND(SUM( spend )/SUM(fb_pixel_purchase), 6) AS cost_per_purchase,

              ROUND(SUM( fb_pixel_purchase )/SUM(spend), 6) AS roi,
              ROUND(SUM( fb_pixel_purchase )/SUM(spend), 6) AS roas
      FROM `campaign_statistic`
      WHERE dt BETWEEN '2019-07-08' AND '2019-07-08'
      GROUP BY campaign_id
     ) statistic
     ON statistic.campaign_id = campaign.campaign_id
WHERE campaign.account_id = 1000623940119431 AND
      (campaign.campaign_id LIKE '%%' OR campaign.name LIKE '%%') 
ORDER BY id ASC;

One place to begin is an index on campaign(account_id, campaign_id, name). Then for the subquery, campaign_statistic(dt, campaign_id).

Depending on the data, you might find it better to "unroll" the aggregation. That is, do the JOIN first and then aggregate over both tables.

Upvotes: 3

Related Questions