Hi there.

I've been wrestling with this problem for quite a while now and the automatic mails with 'Slow Query' warnings are still popping in. Basically, I have Blogs with a corresponding table as well as a table that keeps track of how many times each Blog has been viewed. This last table has a huge amount of records since this page is relatively high traffic and it logs every hit as an individual row. I have tried with indexes on the fields that are included in the WHERE clause, but it doesn't seem to help. I have also tried to clean the table each week by removing old (> 1.weeks) records. SO, I'm asking you guys, how would you solve this?

The query that I know is causing the slowness is generated by Rails and looks like this:

SELECT count(*) AS count_all

FROM blog_views

WHERE (created_at >= '2010-01-01 00:00:01' AND blog_id = 1);

The tables have the following structures:

CREATE TABLE IF NOT EXISTS 'blogs' (

'id' int(11) NOT NULL auto_increment,

'name' varchar(255) default NULL,

'perma_name' varchar(255) default NULL,

'author_id' int(11) default NULL,

'created_at' datetime default NULL,

'updated_at' datetime default NULL,

'blog_picture_id' int(11) default NULL,

'blog_picture2_id' int(11) default NULL,

'page_id' int(11) default NULL,

'blog_picture3_id' int(11) default NULL,

'active' tinyint(1) default '1',

PRIMARY KEY ('id'),

KEY 'index_blogs_on_author_id' ('author_id')

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

And

CREATE TABLE IF NOT EXISTS 'blog_views' (

'id' int(11) NOT NULL auto_increment,

'blog_id' int(11) default NULL,

'ip' varchar(255) default NULL,

'created_at' datetime default NULL,

'updated_at' datetime default NULL,

PRIMARY KEY ('id'),

KEY 'index_blog_views_on_blog_id' ('blog_id'),

KEY 'created_at' ('created_at')

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;