mysql - SQL query: Speed up for huge tables -

we have table 25,000,000 rows called 'events' having following schema:

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

the following query takes long (> 2000 seconds):

select count(*) counted_events, country_code events campaign_id` in (597)  groupy city, country_code order counted_events 

we found out it's because of group by part.

there index idx_campaign_id_city_country_code on (campaign_id, city, country_code) used.

maybe can suggest solution speed up?

update:

'explain' shows out of many possible index mysql uses one: 'idx_campaign_id_city_country_code', rows shows: '471304' , 'extra' shows: 'using where; using temporary; using filesort' –

here 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, think has been solved:

looking @ pasted query here again realized forget mention here there 1 more column in select called 'country_name'. query slow (including country_name), i'll leave out , performance of query absolutely ok. sorry mistake!

so thank helpful comments, i'll upvote answers! there helpful additions, apply (like changing types etc).

without seeing explain says it's long distance shot, anyway:

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

post entire explain output