Database design proposal for relational data
I have been struggling with this issue for a long time and the automatic "Slow Query" warning emails still appear. Basically I have blogs with a matching table and also a table that keeps track of how many times each blog has been viewed. This last table has a huge number of records as this page has relatively high traffic and records each hit as a separate row. I've tried with indexes on fields that are included in the WHERE clause, but it doesn't seem to help. I also tried to clear the table every week by deleting old records (> 1.weeks). So I ask you guys how would you solve this?
The request 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 ;
a source to share
Most likely you will need one index on both blog_id and created_at columns
Not two indexes with one column each ...
...
KEY 'whizzy' ('blog_id', 'created_at'),
...
From CREATE TABLE you can have compound indexes / keys:
... {INDEX|KEY} [index_name] [index_type] (index_col_name,...) ...
and also try changing the order if that doesn't work.
a source to share
I would not write each hit as a unique string ....
If what you want to do is only count how many times a blog is viewed, why don't you create one row on the blog and add the view_count field to your table. Then only that field is updated ....
If you need to track users / ips and you want to know how many times each user has clicked on blogs, then create an additional table in a structure like this:
id (pk)
blog_id
user_id / ip_address
dates
hit_counts
With a structure like this, you can have an idea of how many times a blog has been viewed, as well as how many users and how many times each user has clicked ...
Or, if you really want to create rows for every hit, and these indexes didn't speed up the process, you could consider a more powerful server or other database management system.
Hope this helps!
a source to share
My idea is the same as piero, but some difference.
You can add one column to the blog table named "hits" and always increment this field when hitting it and insert a new record into the blog_view table, as you are doing now.
if you don't need to run a query to count views, and you can also view their details when needed.
a source to share