Skip navigation links

MySQL Forums :: MyISAM :: Help to optimize query


Advanced Search

Help to optimize query
Posted by: Daniel Morais ()
Date: November 27, 2012 12:27PM

First of all, I'm sorry if this isn't the correct forum for this kind of thing, but since I'm using MyISAM, I'm posting here :)

I have a query which is taking more than 10 minutes to finish and I'm not sure what else can be done to optimize either the query or the database.

The query is:

SELECT 'View' Name, view.fk_id_affiliate_site ID, Count(*) Quantity, campaign.billing_type Billing_type, campaign.id_campaign Id_campaign,
                        campaign.percent_cut Percent_campaign
                        FROM `spd_log_view` view
                        INNER JOIN spd_unit unit ON ( view.fk_id_unit = unit.id_unit AND unit.fk_id_ad_format = 2 )
                        INNER JOIN spd_ad ad ON ( unit.fk_id_ad = ad.id_ad )
                        INNER JOIN spd_campaign campaign ON ( ad.fk_id_campaign = campaign.id_campaign )
                        INNER JOIN spd_affiliate_site site ON ( site.id_affiliate_site = view.fk_id_affiliate_site )
                        WHERE timestamp BETWEEN 1353898800.0000 AND 1353985200 GROUP BY view.fk_id_affiliate_site, campaign.id_campaign
                        UNION
                        SELECT 'Click' Name, click.fk_id_affiliate_site ID, Count(*) Quantity, campaign.cpc CPC, campaign.cpm CPM, campaign.billing_type Billing_type, campaign.id_campaign Id_campaign,
                        IF(site.percent_cut = 0,campaign.percent_cut ,site.percent_cut) Percent, site.percent_cut Percent_site, campaign.percent_cut Percent_campaign
                        FROM `spd_log_click` click
                        INNER JOIN spd_unit unit ON ( click.fk_id_unit = unit.id_unit AND unit.fk_id_ad_format = 2 )
                        INNER JOIN spd_ad ad ON ( unit.fk_id_ad = ad.id_ad )
                        INNER JOIN spd_campaign campaign ON ( ad.fk_id_campaign = campaign.id_campaign )
                        INNER JOIN spd_affiliate_site site ON ( site.id_affiliate_site = click.fk_id_affiliate_site )
                        WHERE timestamp BETWEEN 1353898800.0000 AND 1353985200 GROUP BY click.fk_id_affiliate_site, campaign.id_campaign


Explain output:
+----+--------------+------------+--------+----------------------------------------------+-----------+---------+-------------------------------------------+----------+----------------------------------------------+
| id | select_type  | table      | type   | possible_keys                                | key       | key_len | ref                                       | rows     | Extra                                        |
+----+--------------+------------+--------+----------------------------------------------+-----------+---------+-------------------------------------------+----------+----------------------------------------------+
|  1 | PRIMARY      | view       | range  | fk_id_affiliate_site,fk_id_unit,timestamp    | timestamp | 8       | NULL                                      | 32725996 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY      | unit       | eq_ref | PRIMARY,t_anuncio_t_pecas_fk,fk_id_ad_format | PRIMARY   | 4       | prod_front_end.view.fk_id_unit            |        1 | Using where                                  |
|  1 | PRIMARY      | ad         | eq_ref | PRIMARY,t_campanha_t_grupo_anuncio_fk        | PRIMARY   | 4       | prod_front_end.unit.fk_id_ad              |        1 |                                              |
|  1 | PRIMARY      | site       | eq_ref | PRIMARY                                      | PRIMARY   | 4       | prod_front_end.view.fk_id_affiliate_site  |        1 |                                              |
|  1 | PRIMARY      | campaign   | eq_ref | PRIMARY                                      | PRIMARY   | 4       | prod_front_end.ad.fk_id_campaign          |        1 |                                              |
|  2 | UNION        | click      | range  | fk_id_affiliate_site,fk_id_unit,timestamp    | timestamp | 8       | NULL                                      |    68398 | Using where; Using temporary; Using filesort |
|  2 | UNION        | unit       | eq_ref | PRIMARY,t_anuncio_t_pecas_fk,fk_id_ad_format | PRIMARY   | 4       | prod_front_end.click.fk_id_unit           |        1 | Using where                                  |
|  2 | UNION        | ad         | eq_ref | PRIMARY,t_campanha_t_grupo_anuncio_fk        | PRIMARY   | 4       | prod_front_end.unit.fk_id_ad              |        1 |                                              |
|  2 | UNION        | site       | eq_ref | PRIMARY                                      | PRIMARY   | 4       | prod_front_end.click.fk_id_affiliate_site |        1 |                                              |
|  2 | UNION        | campaign   | eq_ref | PRIMARY                                      | PRIMARY   | 4       | prod_front_end.ad.fk_id_campaign          |        1 |                                              |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL                                         | NULL      | NULL    | NULL                                      |     NULL |                                              |
+----+--------------+------------+--------+----------------------------------------------+-----------+---------+-------------------------------------------+----------+----------------------------------------------+
11 rows in set (0.04 sec)


I'm an AWS RDS user, so I installed MySQL 5.5 with all the default variables, and changed only these two:

mysql> show variables LIKE '%table_size';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 67108864 |
| tmp_table_size | 67108864 |
+---------------------+----------+

Any help will be very much appreciated.

Thanks in advance.

Options: ReplyQuote


Subject Views Written By Posted
Help to optimize query 1440 Daniel Morais 11/27/2012 12:27PM
Re: Help to optimize query 764 Rick James 12/02/2012 09:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.