Professional Web Applications Themes

tables vs databases - MySQL

This is probably a stupid question but is how is there much of a difference in performance in storing all tables in the one database and spreading the info over a few databases. In other words: 1 database with 50 tables VS 5 databases with 10 tables each? Cheers, Ciaran...

  1. #1

    Default tables vs databases

    This is probably a stupid question but is how is there much of a
    difference in performance in storing all tables in the one database and
    spreading the info over a few databases.

    In other words:
    1 database with 50 tables VS 5 databases with 10 tables each?

    Cheers,
    Ciaran

    cronoklee@hotmail.com Guest

  2. #2

    Default Re: tables vs databases

    [email]cronokleehotmail.com[/email] wrote:
    > This is probably a stupid question but is how is there much of a
    > difference in performance in storing all tables in the one database and
    > spreading the info over a few databases.
    >
    > In other words:
    > 1 database with 50 tables VS 5 databases with 10 tables each?
    >
    > Cheers,
    > Ciaran
    >
    I don't think there's that much performance difference. However, I keep
    unrelated information in different databases for other reasons. For
    instance, if I wanted to move my store to another server, I could just
    move that database.

    Or I can backup databases which change regularly on a daily basis, and
    those which change occasionally weekly.

    Also, user maintenance is easier when dealing with different databases.
    Yes, you can assign privileges at the table level in MySQL - but it's
    a lot easier to grant or deny access to one database than 20 tables. A
    lot easier to keep track of, also.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: tables vs databases

    [email]cronokleehotmail.com[/email] wrote:
    > This is probably a stupid question but is how is there much of a
    > difference in performance in storing all tables in the one database and
    > spreading the info over a few databases.
    >
    > In other words:
    > 1 database with 50 tables VS 5 databases with 10 tables each?
    No difference in performance, assuming the 5 databases all live on the
    same MySQL server instance. I suggest keeping them together in one
    database for simplicity. It'll be easier to write queries, perform
    backups, etc.

    Regards,
    Bill K.
    Bill Karwin Guest

  4. #4

    Default Re: tables vs databases

    >This is probably a stupid question but is how is there much of a
    >difference in performance in storing all tables in the one database and
    >spreading the info over a few databases.
    >
    >In other words:
    >1 database with 50 tables VS 5 databases with 10 tables each?
    For MyISAM tables, a table consists of 3 files in the directory for
    each database. Too many files in a directory can slow things down.
    If you have 1,000 tables in a database (directory size of about
    60kB), it's time to think about splitting it up. If you have 100,000
    tables in a database, (directory size of about 6MB), you really
    need to split it up. I'm not sure if the same issue exists with
    InnoDB with all the tables in a common file.

    50 tables with reasonably short names are unlikely to make a directory
    bigger than the minimum block size of a directory (4k, 8k, or 16k
    on some UNIX versions) so there's really not much need to split
    them up.

    Although some of this discussion sounds like it's UNIX-specific,
    the same issue exists with Windows.

    On the other hand, MySQL caches privileges for the current database,
    but not for others. If your queries have a lot of other-database
    references, privilege checking might slow things down a bit. I
    tend to put tables likely to be accessed by the same users (often
    meaning: same web application) together in the same database, and
    stuff with very different security restrictions in different
    databbases.

    If you find yourself putting variables into a table name (e.g.
    naming tables for the month and year of the data they contain, and
    having a whole series of tables with the same structure), that is
    likely to cause a lot of grief if you ever want a query to handle
    more than a months worth of data. It is usually better to add a
    column to the table containing what would be the table name, or the
    variable part of it, and collect it all into one big table.
    Gordon Burditt Guest

  5. #5

    Default Re: tables vs databases

    Brilliant, Thanks a lot guys - i was worried I was slowing down my
    whole site by sticking everything in the one database but it sonds like
    I'm OK after all.
    Cheers for the help!
    Ciarán

    cronoklee@hotmail.com Guest

  6. #6

    Default Re: tables vs databases


    Jerry Stuckle wrote:
     
    >
    > I don't think there's that much performance difference. However, I keep
    > unrelated information in different databases for other reasons. For
    > instance, if I wanted to move my store to another server, I could just
    > move that database.
    >
    > Or I can backup databases which change regularly on a daily basis, and
    > those which change occasionally weekly.
    >
    > Also, user maintenance is easier when dealing with different databases.
    > Yes, you can assign privileges at the table level in MySQL - but it's
    > a lot easier to grant or deny access to one database than 20 tables. A
    > lot easier to keep track of, also.
    >[/ref]
    I was under the impression that one had to connect to a particular
    database in order to access it and that one could only work with one at
    a time (as far as running distinct queries goes). Is it possible to
    JOIN tables on different databases and to maintain FK contraints on
    table in more than one database?

    If not, surely this is a major reason for keeping related tables in a
    single database?

    Captain Guest

  7. #7

    Default Re: tables vs databases

    On Mon, 04 Sep 2006 06:26:58 -0700, Captain Paralytic wrote:
     
    >>
    >> I don't think there's that much performance difference. However, I keep
    >> unrelated information in different databases for other reasons. For
    >> instance, if I wanted to move my store to another server, I could just
    >> move that database.
    >>
    >> Or I can backup databases which change regularly on a daily basis, and
    >> those which change occasionally weekly.
    >>
    >> Also, user maintenance is easier when dealing with different databases.
    >> Yes, you can assign privileges at the table level in MySQL - but it's
    >> a lot easier to grant or deny access to one database than 20 tables. A
    >> lot easier to keep track of, also.
    >>[/ref]
    > I was under the impression that one had to connect to a particular
    > database in order to access it and that one could only work with one at
    > a time (as far as running distinct queries goes).[/ref]

    Not at all. You can easily access tables across databases by fully
    qualifying table names. SomeDB.SomeTable
     

    Yes.
     

    The major reason for keeping related tables in a single database is to
    keep your mind comfortably wrapped around it. When you start having
    difficulty understanding and dealing the system, then you probably need to
    break it up into simpler, more numerous databases - each with fewer tables.

    The idea is to help *you* to stay in control. MySQL is not likely to care
    one way or the other.

    Simpleton that I am, I tend to view databases with more than a dozen
    tables as well as tables with more than a dozen fields to be inherently
    unworkable and (probably!) badly designed.

    Your mileage may vary ;-)
    -Tom



    Thomas Guest

  8. #8

    Default Re: tables vs databases

    Captain Paralytic wrote: 
    >>
    >>I don't think there's that much performance difference. However, I keep
    >>unrelated information in different databases for other reasons. For
    >>instance, if I wanted to move my store to another server, I could just
    >>move that database.
    >>
    >>Or I can backup databases which change regularly on a daily basis, and
    >>those which change occasionally weekly.
    >>
    >>Also, user maintenance is easier when dealing with different databases.
    >> Yes, you can assign privileges at the table level in MySQL - but it's
    >>a lot easier to grant or deny access to one database than 20 tables. A
    >>lot easier to keep track of, also.
    >>[/ref]
    >
    > I was under the impression that one had to connect to a particular
    > database in order to access it and that one could only work with one at
    > a time (as far as running distinct queries goes). Is it possible to
    > JOIN tables on different databases and to maintain FK contraints on
    > table in more than one database?
    >
    > If not, surely this is a major reason for keeping related tables in a
    > single database?
    >[/ref]

    That's why I said I keep UNRELATED INFORMATION in different databases.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  9. #9

    Default Re: tables vs databases

    Thomas Bartkus wrote: 
    >>
    >>I was under the impression that one had to connect to a particular
    >>database in order to access it and that one could only work with one at
    >>a time (as far as running distinct queries goes).[/ref]
    >
    >
    > Not at all. You can easily access tables across databases by fully
    > qualifying table names. SomeDB.SomeTable
    >

    >
    >
    > Yes.
    >

    >
    >
    > The major reason for keeping related tables in a single database is to
    > keep your mind comfortably wrapped around it. When you start having
    > difficulty understanding and dealing the system, then you probably need to
    > break it up into simpler, more numerous databases - each with fewer tables.
    >
    > The idea is to help *you* to stay in control. MySQL is not likely to care
    > one way or the other.
    >
    > Simpleton that I am, I tend to view databases with more than a dozen
    > tables as well as tables with more than a dozen fields to be inherently
    > unworkable and (probably!) badly designed.
    >
    > Your mileage may vary ;-)
    > -Tom
    >
    >
    >[/ref]

    I don't go by numbers. I go by relationships. I've had databases with 
    databases with 3 tables and a dozen columns. And these are required -
    I've been doing RDB design for over 20 years, and have done some quite
    large ones.

    Yes, MySQL can work across databases, but inter-database joins are quire
    costly. Additionally, this is a MySQL extension not supported by most
    other databases.

    But breaking up related information into multiple databases does not
    make things simpler - it just complicates matters because you also need
    to keep track of which database some information is in, not just the
    table and column.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  10. #10

    Default Re: tables vs databases

    On Mon, 04 Sep 2006 14:01:19 -0400, Jerry Stuckle wrote:
     
    >>
    >>
    >> Not at all. You can easily access tables across databases by fully
    >> qualifying table names. SomeDB.SomeTable
    >>
    >> 
    >>
    >>
    >> Yes.
    >>
    >> 
    >>
    >>
    >> The major reason for keeping related tables in a single database is to
    >> keep your mind comfortably wrapped around it. When you start having
    >> difficulty understanding and dealing the system, then you probably need
    >> to break it up into simpler, more numerous databases - each with fewer
    >> tables.
    >>
    >> The idea is to help *you* to stay in control. MySQL is not likely to
    >> care one way or the other.
    >>
    >> Simpleton that I am, I tend to view databases with more than a dozen
    >> tables as well as tables with more than a dozen fields to be inherently
    >> unworkable and (probably!) badly designed.
    >>
    >> Your mileage may vary ;-)
    >> -Tom
    >>
    >>
    >>
    >>[/ref]
    > I don't go by numbers. I go by relationships. I've had databases with 
    > databases with 3 tables and a dozen columns. And these are required -
    > I've been doing RDB design for over 20 years, and have done some quite
    > large ones.
    >
    > Yes, MySQL can work across databases, but inter-database joins are quire
    > costly. Additionally, this is a MySQL extension not supported by most
    > other databases.
    >
    > But breaking up related information into multiple databases does not
    > make things simpler - it just complicates matters because you also need
    > to keep track of which database some information is in, not just the
    > table and column.[/ref]

    Far be it from me to suggest splitting tables with *related* information
    across databases. And if you had a frequent need for inter-table joins,
    that would certainly be a clue that you were doing that.

    If one can produce a meaningful and useful relationship diagram for a database
    with 100 distinct tables, and keep it consistent and free of redundant
    information, then I would say fine. But it's much like having hundreds of
    lines of code in one single function. Usually it means something ain't right!

    Thomas Bartkus
    Thomas Guest

  11. #11

    Default Re: tables vs databases

    Jerry Stuckle <net> wrote: 

    Maybe it's just my poor English, but I don't understand this sentence.
    Fact is, in MySQL you can JOIN tables from different databases at the
    same cost than tables in the same database.

    [this is not to be mixed with the FEDERATED storage engine. FEDERATED
    allows to access remote tables as local ones. This comes at an extra
    cost, especially for JOINs]
     

    The reason is, that MySQL uses the term 'database' slightly different
    than other DBMS do. In MySQL a database is just a namespace for other
    objects like tables or views. So in the end, *all* tables in *all*
    databases live in the same MySQL instance and can be used in arbitrary
    combinations. However, to access a table outside your current database
    you have to fully qualify its name as <database>.<table>.

    In newer MySQL versions (5.0 and up) the term 'database' will be less
    used in favour of 'schema'. This is to avoid ambiguities for non-MySQL
    DBAs.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  12. #12

    Default Re: tables vs databases

    Thomas Bartkus wrote: 
    >>
    >>I don't go by numbers. I go by relationships. I've had databases with 
    >>databases with 3 tables and a dozen columns. And these are required -
    >>I've been doing RDB design for over 20 years, and have done some quite
    >>large ones.
    >>
    >>Yes, MySQL can work across databases, but inter-database joins are quire
    >>costly. Additionally, this is a MySQL extension not supported by most
    >>other databases.
    >>
    >>But breaking up related information into multiple databases does not
    >>make things simpler - it just complicates matters because you also need
    >>to keep track of which database some information is in, not just the
    >>table and column.[/ref]
    >
    >
    > Far be it from me to suggest splitting tables with *related* information
    > across databases. And if you had a frequent need for inter-table joins,
    > that would certainly be a clue that you were doing that.
    >
    > If one can produce a meaningful and useful relationship diagram for a database
    > with 100 distinct tables, and keep it consistent and free of redundant
    > information, then I would say fine. But it's much like having hundreds of
    > lines of code in one single function. Usually it means something ain't right!
    >
    > Thomas Bartkus[/ref]

    Not at all. Using some of the graphic utilities around (even Visio
    works) it's quite easy to keep track of tables and relationships.

    And no, I completely disagree with the statement "Usually it means
    something ain't right!". If you ever have to do links between
    databases, that means something isn't right! And there is no comparison
    between databases and functions.

    I can tell you've never worked on a large database project. Large
    projects often require large databases with lots of tables and columns.
    Whether the db design is good or bad has no relationship to the number
    of columns or tables required - you can do a good design with a lot of
    tables, or a bad design with a few tables, for instance.

    And it's not at all unusual, even for a web-based system, to have 30-40
    tables. A couple of non-profits I'm currently working on come to mind.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  13. #13

    Default Re: tables vs databases

    Axel Schwenke wrote: 
    >
    >
    > Maybe it's just my poor English, but I don't understand this sentence.
    > Fact is, in MySQL you can JOIN tables from different databases at the
    > same cost than tables in the same database.
    >[/ref]

    It's not your English - it's my spelling :-). That should have been
    "quite costly". Joining tables between databases takes a lot more cpu.
     
    >
    >
    > The reason is, that MySQL uses the term 'database' slightly different
    > than other DBMS do. In MySQL a database is just a namespace for other
    > objects like tables or views. So in the end, *all* tables in *all*
    > databases live in the same MySQL instance and can be used in arbitrary
    > combinations. However, to access a table outside your current database
    > you have to fully qualify its name as <database>.<table>.
    >[/ref]

    No, a database and an instance are different, even in other databases.
    For instance, in DB2 you can have multiple instances on a system (which
    you can't do as easily with MySQL) and each instance can have multiple
    databases.

    And I don't find it advisable to create database designs which require
    joins across databases for the aforementioned reasons.
     

    Which are something different yet.
     


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  14. #14

    Default Re: tables vs databases

    >No, a database and an instance are different, even in other databases. 

    Unless you can't help it. Usually this means you are trying to
    join against Somebody Else's Database (e.g. the Social Security
    Administration, or a credit bureau's database), and they won't let
    you have all of it except at extreme cost (monetary, processing
    time, or political capital), and insist you use their klunky method
    of accessing it.

    You may well be stuck with doing the equivalent of cross-database
    joins when they are administered by different mutually-suspicious
    organizations and there is no common parent to make them play nice
    together.


    Gordon Guest

Similar Threads

  1. populating dropdown using tables from two databases
    By cfdyn in forum Coldfusion Database Access
    Replies: 6
    Last Post: August 20th, 06:51 AM
  2. QUERY two tables from different databases
    By WhozitsPop in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 25th, 03:15 PM
  3. No tables show up under databases/connections
    By Sunder27 in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: March 24th, 03:52 AM
  4. Joining Tables Across Databases
    By Wilkinson, Marcus in forum Informix
    Replies: 3
    Last Post: October 31st, 11:04 AM
  5. Replies: 5
    Last Post: August 19th, 12:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139