List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:May 24 2007 6:32pm
Subject:Re: design choice - quite many tables
View as plain text  
On Thu, May 24, 2007 2:12, Przemys?aw Klein said:
> Wm Mussatto wrote:
>> Assuming you are using MYISAM table types, each table requires at least
>> three files on the disk.  If they are in one database they will all be
>> in
>> one directory (how fast is your OS at finding the files in its directory
>> structure?).
>> Are they going to be opened at the same time (how many file handles can
>> you have open at once?)?
>> If separate databases, how do you intend to connect to them (single
>> connection specifying database or multiple connections).
>> My feeling is that you would not gain anything by having separate
>> databases.
>> Good luck.
> Thanks Wiliam.
> OS resources are sufficient. We connect through connection pool, so it
> also shouldn't be a problem. The main reason of separating data into
> several databases is easier management (in particular: backup and
> recovery) and easier developer usability. I can hardly imagine to manage
> database with lets say 500 tables. I don't know if there is a way to
> 'catalogue'/organize tables within one database (namespaces/schema?).
> The second argument is that data from past years isn't modified often
> and we can apply different backup policies.
> regards,
> --
> _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/
> Przemek Klein (p.klein@stripped)
There is nothing like knowing what your conditions are.  When I spoke of
connections I come from Perl DBI and each database (simultaneously
accessed) would require a separate database handle be created which in
turn would tie up a connection.  You can get around this but its awkward. 
I guess I'd pencil out the queries and see if you would have to ask
questions between the databases in the same query, again just awkward, not
a show stopper.  As for backup, you could backup on a table by table
basis, but then that gets a bit more awkward.  If the historical tables
are truely read-only there are special table types that might help there.
William R. Mussatto
Systems Engineer

design choice - quite many tablesPrzemysław Klein22 May
  • Re: design choice - quite many tablesBrent Baisley22 May
  • Re: design choice - quite many tablesMartijn Tonies22 May
    • Re: design choice - quite many tablesPrzemysław Klein23 May
      • Re: design choice - quite many tablesWm Mussatto23 May
        • Re: design choice - quite many tablesPrzemys?aw Klein24 May
          • Re: design choice - quite many tablesWm Mussatto24 May