List:General Discussion« Previous MessageNext Message »
From:Michael Addyman Date:February 10 2009 6:34pm
Subject:Re: InnoDB: Thousands of Tables or Hundreds of Databases?
View as plain text  
Martin, I'm guessing you mean 1 database per table type.

On Tue, Feb 10, 2009 at 5:17 PM, Martin Gainty <mgainty@stripped> wrote:

>
> 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