How to get acceptable/repeatable performance on huge table

[Peter Stridsberg]

In a report table, the following query often takes 2 minutes (which is fine with us), but sometimes "never" finishes (has been going for a few hours when I cancelled):

select adId, count(userUId) from UserVisits where adId < 25000 group by adId;

The db is a slave - replicated from a master db. In the master DB, the table is frequently updated (>1000 times/second). The table is huge (212 million rows).

Usually, the query runs for ever until I break it and do something like:
select count(*) from UserVisits
which takes about 15 minutes after which the original query just take 2 minutes. However, sometimes also the count(*) takes "for ever".

When the query takes 2 minutes it will run that fast again and again. Surprisingly, this pattern remains true even if I run RESET QUERY CACHE.

Note: I added "where adId < 25000" becasue it seemed to be a lot quicker. It include all Ads.

Questions:
1. Any general idea as to how to get better performance always with the above query?
2. If there is a potential problem with the large amounts of waiting updates, can i pose the query with a hint that says I don't care if I get old or new data (complete accuracy is not needed for me).


TABLE:

Table: UserVisits
Create Table: CREATE TABLE `UserVisits` (
`userUId` bigint(20) unsigned NOT NULL,
`adId` int(10) unsigned NOT NULL,
`lastRequest` datetime NOT NULL,
`validImps` int(4) unsigned NOT NULL,
`validClicks` int(2) unsigned NOT NULL,
`validImpsToday` int(4) unsigned NOT NULL,
`validClicksToday` int(2) unsigned NOT NULL,
PRIMARY KEY (`userUId`,`adId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SERVER:
mysql Ver 14.14 Distrib 5.5.19, for Linux (x86_64) using readline 5.1


SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size';
+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
| innodb_log_file_size | 268435456 |
+----------------------+-----------+
:


RAM memory:
$ free
total used free shared buffers cached
Mem: 65995448 55999876 9995572 0 520592 3724636
-/+ buffers/cache: 51754648 14240800
Swap: 2031608 225048 1806560