SQL query: Speed up for huge tables

Question

We have a table with about 25,000,000 rows called 'events' having the following schema:

TABLE events
- campaign_id   : int(10)
- city      : varchar(60)
- country_code  : varchar(2)

The following query takes VERY long (> 2000 seconds):

SELECT COUNT(*) AS counted_events, country_code
FROM events
WHERE campaign_id` in (597) 
GROUPY BY city, country_code
ORDER BY counted_events

We found out that it's because of the GROUP BY part.

There is already an index idx_campaign_id_city_country_code on (campaign_id, city, country_code) which is used.

Maybe someone can suggest a good solution to speed it up?

Update:

'Explain' shows that out of many possible index MySql uses this one: 'idx_campaign_id_city_country_code', for rows it shows: '471304' and for 'Extra' it shows: 'Using where; Using temporary; Using filesort' –

Here is the whole result of EXPLAIN:

• id: '1'
• select_type: 'SIMPLE'
• table: 'events'
• type: 'ref'
• possible_keys: 'index_campaign,idx_campaignid_paid,idx_city_country_code,idx_city_country_code_campaign_id,idx_cid,idx_campaign_id_city_country_code'
• key: 'idx_campaign_id_city_country_code'
• key_len: '4'
• ref: 'const'
• rows: '471304'
• Extra: 'Using where; Using temporary; Using filesort'

UPDATE:

Ok, I think it has been solved:

Looking at the pasted query here again I realized that I forget to mention here that there was one more column in the SELECT called 'country_name'. So the query was very slow then (including country_name), but I'll just leave it out and now the performance of the query is absolutely ok. Sorry for that mistake!

So thank you for all your helpful comments, I'll upvote all the good answers! There were some really helpful additions, that I probably also we apply (like changing types etc).

Answer 1

without seeing what EXPLAIN says it's a long distance shot, anyway:

1. make an index on (city,country_code)
2. see if there's a way to use partitioning, your table is getting rather huge
3. if country code is always 2 chars change it to char
4. change numeric indexes to unsigned int

post entire EXPLAIN output