0

I have a query on a large table(over 2 million rows) which takes ~10 seconds to complete. Is there any way to optimize it? The query is as below:

SELECT
    DATE_FORMAT(date0, '%Y-%m' ) AS Yr_Mo, 
    DATE_FORMAT(date0, '%p' ) AS AM_PM,
    province AS Province,
    SUM( IF( top_ads + left_ads =0, 1, 0 ) ) AS pagesWithRightAdsOnly, 
    AVG( top_ads ) AS top_ads, 
    AVG( left_ads ) AS left_ads, 
    AVG( right_ads ) AS right_ads
FROM ad_counts
GROUP BY Yr_Mo, AM_PM, Province

The table 'ad_counts':

date0 (timestamp)
province(varchar)
keyword_id
number_ads(int)
top_ads (int)
left_ads (int)
right_ads (int)

Index on date0, but date0 is not unique.

Any thoughts?

Edit: EXPLAIN:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  baidu_pro_ad_counts ALL NULL    NULL    NULL    NULL    2160752 Using temporary; Using filesort
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
xiaolong
  • 3,396
  • 4
  • 31
  • 46
  • 1
    do you have any indexes on the table? – Taryn Nov 05 '12 at 22:41
  • @bluefeet, Yes, 'date0' is indexed. I also tried to create 2 columns named Yr_Mo and AM_PM and index on them, but didn't get better performance. – xiaolong Nov 05 '12 at 22:44
  • Can you limit the query to a subset of the data? Currently there isn't much opportunity for optimization since it is pulling results for all records in the table and must read each record. Can you post the output of an EXPLAIN query? – drew010 Nov 05 '12 at 22:55
  • Create a composite index on `(Yr_Mo, AM_PM, Province)`. – eggyal Nov 05 '12 at 23:05
  • 1
    The best way to optimize would be to have a separate table containing averages for every month or something like that, theses averages would be calculated only once a month. – Alexandre Lavoie Nov 06 '12 at 01:21
  • @eggyal, thanks, I just did that, but mysql still spends lots of time 'Sorting result' and 'Copying to tmp table'. Any further thoughts? Thanks!! – xiaolong Nov 06 '12 at 01:23
  • what is your sort buffer size ? – simplifiedDB Jan 28 '18 at 16:22

0 Answers0