Skip navigation links

MySQL Forums :: Database Design & Data Modelling :: Indexed key or multiple schemas


Advanced Search

Indexed key or multiple schemas
Posted by: Jon Ivmark ()
Date: June 23, 2011 01:47PM

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

Options: ReplyQuote


Subject Written By Posted
Indexed key or multiple schemas Jon Ivmark 06/23/2011 01:47PM
Re: Indexed key or multiple schemas Rick James 06/24/2011 10:50PM
Re: Indexed key or multiple schemas Jon Ivmark 06/30/2011 12:41AM
Re: Indexed key or multiple schemas Rick James 06/30/2011 07:48PM
Re: Indexed key or multiple schemas Rick James 08/28/2011 11:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.