I have a query that's used in a reporting system of ours that sometimes runs quicker than a second, and other times takes 1 to 10 minutes to run.

Here's the entry from the slow query log:

# Query_time: 543  Lock_time: 0  Rows_sent: 0  Rows_examined: 124948974
use statsdb;
SELECT count(distinct Visits.visitorid) as 'uniques'
 FROM Visits,Visitors
 WHERE Visits.visitorid=Visitors.visitorid
     and candidateid in (32)
     and visittime>=1275721200 and visittime<=1275807599 
     and (omit=0 or omit>=1275807599) 
     AND Visitors.segmentid=9 
     AND Visits.visitorid NOT IN 
         (SELECT Visits.visitorid 
          FROM Visits,Visitors 
          WHERE Visits.visitorid=Visitors.visitorid
              and candidateid in (32) 
              and visittime<1275721200 
              and (omit=0 or omit>=1275807599) 
              AND Visitors.segmentid=9);

It's basically counting unique visitors, and it's doing that by counting the visitors for today and then substracting those that have been here before. If you know of a better way to do this, let me know.

I just don't understand why sometimes it can be so quick, and other times takes so long - even with the same exact query under the same server load.

Here's the EXPLAIN on this query. As you can see it's using the indexes I've set up:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY Visits  range   visittime_visitorid,visitorid   visittime_visitorid 4   NULL    82500   Using where; Using index
1   PRIMARY Visitors    eq_ref  PRIMARY,cand_visitor_omit   PRIMARY 8   statsdb.Visits.visitorid    1   Using where
2   DEPENDENT SUBQUERY  Visits  ref visittime_visitorid,visitorid   visitorid   8   func    1   Using where
2   DEPENDENT SUBQUERY  Visitors    eq_ref  PRIMARY,cand_visitor_omit   PRIMARY 8   statsdb.Visits.visitorid    1   Using where

I tried to optimize the query a few weeks ago and came up with a variation that consistently took about 2 seconds, but in practice it ended up taking more time since 90% of the time the old query returned much quicker. Two seconds per query is too long because we are calling the query up to 50 times per page load, with different time periods.

Could the quick behavior be due to the query being saved in the query cache? I tried running 'RESET QUERY CACHE' and 'FLUSH TABLES' between my benchmark tests and I was still getting quick results most of the time.

Note: last night while running the query I got an error: Unable to save result set. My initial research shows that may be due to a corrupt table that needs repair. Could this be the reason for the behavior I'm seeing?

In case you want server info:

• Accessing via PHP 4.4.4 MySQL 4.1.22
• All tables are InnoDB
• We run optimize table on all tables weekly
• The sum of both the tables used in the query is 500 MB

MySQL config:

key_buffer              = 350M
max_allowed_packet      = 16M
thread_stack            = 128K
sort_buffer             = 14M
read_buffer             = 1M
bulk_insert_buffer_size = 400M
set-variable = max_connections=150
query_cache_limit       = 1048576
query_cache_size        = 50777216
query_cache_type        = 1
tmp_table_size          = 203554432
table_cache             = 120
thread_cache_size       = 4
wait_timeout            = 28800
skip-external-locking
innodb_file_per_table
innodb_buffer_pool_size = 3512M
innodb_log_file_size=100M
innodb_log_buffer_size=4M