List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:February 10 2009 5:17pm
Subject:RE: InnoDB: Thousands of Tables or Hundreds of Databases?
View as plain text  
I vote for 1 table per TableType
this will keep your DB schema consistent with Architecture

Martin 
______________________________________________ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business of Sender.
This transmission is of a confidential nature and Sender does not endorse distribution to
any party other than intended recipient. Sender does not necessarily endorse content
contained within this transmission. 




> Date: Tue, 10 Feb 2009 11:03:46 -0600
> To: mysql@stripped
> From: mos99@stripped
> Subject: Re: InnoDB: Thousands of Tables or Hundreds of Databases?
> 
> At 04:30 AM 2/10/2009, you wrote:
> >Thanks for your comments Mike.
> >
> >The largest table contains 48 columns (objects), the second largest 20
> >columns (users) and all the rest are less than 10 columns. The instance
> >sizes range from 10MB to 1GB.
> >
> >Transactions and row locking are required. Most queries are updates,
> >followed by writes, then reads (application mostly uses memcached and other
> >forms of caching for reads).
> >
> >I have since thought of having 1 table type per database, resulting in
> >'only' ~30 databases; this would be 'easier' to maintain, and each database
> >(containing 1 table type) could be optimised for its ratio of reading :
> >writing : updating.
> >
> >However, this approach would require a LOT of work to re-write the
> >application's database layer.
> >
> >What approach would be best?
> 
> Michael,
>              Does the saying "between a rock and a hard place" sound 
> familiar? :-)
> 
> I feel you're going to have to create a test suite to benchmark both 
> solutions thoroughly before you start on the application code. You're going 
> to find pro's and con's with both designs but after benchmarking you're 
> going to know which one performs better both from a speed viewpoint and 
> maintenance viewpoint. The more time you spend testing the design, the more 
> confidence you'll have that it works and the less chance of throwing it 
> away and starting over later on down the road. Then you'll also be able to 
> present to your client some hard facts about each design.
> 
> Mike
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

_________________________________________________________________
Windows Live™: Keep your life in sync. 
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022009
Thread
InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
  • Re: InnoDB: Thousands of Tables or Hundreds of Databases?mos10 Feb
    • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
    • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
      • Re: InnoDB: Thousands of Tables or Hundreds of Databases?mos10 Feb
        • RE: InnoDB: Thousands of Tables or Hundreds of Databases?Martin Gainty10 Feb
          • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
    • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
      • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Johan De Meersman10 Feb
        • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
      • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Walter Heck10 Feb
        • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
          • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
            • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Johan De Meersman10 Feb
              • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb
  • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Baron Schwartz10 Feb
    • Re: InnoDB: Thousands of Tables or Hundreds of Databases?Michael Addyman10 Feb