SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DB setup question

    Asked this somewhere else but got no answer:

    I was just wondering if someone could give me some input. As a site grows is it better to have multpile DB's with specific tables or all tables in one DB? I've heard that the most straining thing is opening and closing DB connections. But would it be more taxing if one DB has several tables, about 10, with several 100 records in each table, maybe more as the site grows. Right now I have three DB's with a couple tables in each, with expected growth from users. Since tables are sometimes in other DB's but accessed by the same page, I have to close the connection, open a new one, then sometimes close that, to open another. I've heard hosting companies frown upon this for running up server usage, so was just wondering if anyone has input on the matter? I have main content that pulls from one DB and side content that pulls from another, but since they are on the same page, i have to close/open connections.
    Half way to nowhere

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you may want to consider using just one database

    "several 100 records" is trivially small

    100,000 records is modest

    100,000,000 records is getting large
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So several 100,000 records each is fine, say 6 tables?
    Half way to nowhere

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it depends on how the six tables are set up. If they contain all the same columns because you think your data needs split up (many people are under this impression), then no you won't want to do that.

    If you mean you have normailzed your data by splitting up into relevant tables then that is fine.

    indexing your tables accoriding to the columns you want to do most of your joins on will speed up your query times.

    The amount of rows you are speaking of is still on the negligible side.

  5. #5
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Out of the six tables, there are only three that will really grow. The rest will have cron jobs run on them to clean out 7day old entries. I've got userinfo, images, usercomments, itemvotes, voters, news, news comments. The main three that will grow are userinfo, images and itemvotes. For every new user there will be a new row in the userinfo and itemvotes tables and images can be up to four times that since each user can upload four pictures. They be linked through userid's.
    Half way to nowhere

  6. #6
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What would you do then? I asked this over at mysql and no one can seem to answer.
    Half way to nowhere

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by atomicnuke
    What would you do then? I asked this over at mysql and no one can seem to answer.
    where?

    and what was the question again please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Basically you answered my first post saying one DB, then daveman said that might not be good. Not sure if I made the post seem more complex than what I was asking. Basically, how many tables with records can a DB handle with out it slowing down. If I could have all the tables in one DB, I wouldn't have to close/open connections. I think I'll have seven tables, four of them will have the majority of records. But is having four tables with possibly 100,000+ records and the three other ones to much for one DB. I'm not sure on what is to much for one DB.
    Half way to nowhere

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by atomicnuke
    Basically, how many tables with records can a DB handle with out it slowing down.
    if they are properly optimized (e.g. indexed) then you can have as many tables in one database as your file system can hold, and it will run fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, for the answer. How do you know what your file system can hold? something you have to ask your host, or does that go allowing with hosting space? Also for curiosity, when do you need more than one DB?
    Half way to nowhere

  11. #11
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what I was saying is don't have multiple tables with the exact same columns in each.
    some people do that thinking it is better to have small tables even though they contain the exact same columns with less rows rather than one table with more rows since all the columns would be the same.

    the number of rows you are speaking of are basically negiligible for mysql to handle if your tables are properly indexed.

  12. #12
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for the misunderstanding, yeah, my tables are organized and don't contain the same columns. They are linked to the id of the user account, for joins. So is the DB size part of the hosting space provided? I have 10gigs allowance.
    Half way to nowhere

  13. #13
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes the database would count against the space your provider has given you. That space would limit you to the size of your overall database.

    as for more than one database. usually you try to keep all related items in a single database and create a new one if you have completely unrelated information.

    say you had sports stats. you could create one database to hold them all or you could decide that hockey and football belong in their own databases.

    or a more likely scenario, if you were tracking say sports stats and amusement park information. the two don't have enough in common to put them in the same database (if you look at it in that both are forms of entertainment then they do I guess), so that would warrant a second database.

    it is entirely up to you and a single database can fit all your needs if you wish it to do so.

  14. #14
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That sums it up, thanks daveman and r937!
    Half way to nowhere


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
  •