Single database or multiple databases
Posted by: Sanjeev Y
Date: November 03, 2007 03:17AM

Hello All,

Kindly take time to advice me on the design decision
to make for the following problem. I'm using InnoDB
with MySQL 5.0.

I have to design a database whose primary role
is to hold the data of a School. I have almost
1000 tables now and they will contain several
thousands of rows.

Now i have about 64 such schools. The problem
is which would be the best design in the performance
front!?

1) Should i use 64 databases one for each school?
2) Or just use single database and add one more table
to it which has the school reference and all those
1000 tables (or part, where needed) shall be referring
to the school id from that table.

The problem with first approach is we will have 64 databases
and 64 * 1000 tables altogether within a single MySQL instance
running.

The problem with the second approach is there will be several
zillions of rows of all those 64 schools in just single database
with those 1000 tables. And these rows grow with time.
And when we do a search it might take lot of time.

Kindly take time to help me out with the decision on which
will be the best design with innoDB as the MySQL engine in
the performance front.

I have already posted this on "Performance" forum with no success :(
http://forums.mysql.com/read.php?24,180888,180888#msg-180888

Thanks in advance,
Regards,
Sanjeev

Options: ReplyQuote


Subject
Written By
Posted
Single database or multiple databases
November 03, 2007 03:17AM


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.