Suggestion on Database structure for relational data
- by miccet
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 ;
