Indexed key or multiple schemas

[Jon Ivmark]

Hi all, trying to model a schema and I am in need of some advice. Say I've got these two tables:

create table template_stats (
rid integer(10) primary key auto_increment,
site_id integer(10) not null,
template_id integer(10) not null,
interval_start datetime not null,
interval_end datetime not null,
displays integer(10)
) engine=InnoDB;

create table sub_template_stats (
template_rid integer(10) not null,
idx integer(2) not null,
clicks integer(10),
primary key (template_id, idx),
constraint foreign key (template_rid) references template_stats (rid)
) engine=InnoDB;

Now, there will be about 100 different sites (site_id), and for each site I will store around 10-50 rows per hour in the table template_stats. Each row will have around 5 corresponding rows in sub_template_stats, so I might be inserting around 10000 rows into that table per hour.

I'm not sure how long I will keep the data with that granularity, but at least a couple of months, perhaps a year. That would mean somewhere around 20-100M rows in sub_template_stats.

Now to my question. The queries will be always be using one and only one site_id. The set of sites is fairly static, I might add one or two per month tops. The question is if I might see a big performance gain by splitting the data up into one schema per site? So that I get 100 (sub_template_stats) tables with 500000 rows per table instead of one huge table, and with one less indexed field.

The downside is of course the extra work with maintaining the 100 schemas, but since they will all look the same I think I could manage that.

Thankful for any advice

Jon