SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    One or multiple databases?

    Hey all, happy 2005!

    Just a quick question. My site uses three (potentially four or five databases) MySQLs, having no more than half a dozen tables each (one database just has ONE table). This was done because I wanted to keep different information distinct, and seperate from each other, to reduce database size, and to be somewhat more secure (if one db gets corrupted, the rest don't, heh). I've got a daily backup of these databases, the total size is about 4mb.

    The databases hold text, no big blob/file fields as yet. However, they do hold duplicate data, for example I've got a database for 'users' and one for 'forums', but if a user posts on a forum (to the 'forums' database), they need to pull their user data (avatar, custom sig, etc) from the 'users' database. This involves two database connections, and the usual tripe.

    Still in the same users/forums example, it'd be easier to have ONE database, but would this be safer/more efficient? I know that now, to retrieve a post, it's just a simple SELECT, since all the data's in the row. If they were in the same database, I'd use a JOIN using the userID in the posts table, plus the options for that post (avatar, sig, blah blah).

    What's the recommended practice? I can see which is easier, but would there be a perfomance hit using the JOIN (one which we'd notice, heh)?

    And while we're on that subject, how LARGE can a single MySQL database get? My project's fairly small, but could get bigger, I wouldn't like to have it slow down suddenly, because it's working pretty well now. Or would the differences be negligible, unless we get in the thousands of rows league?

    Thanks for any help/advice in advance!

    Cheers,
    Alex ...

  2. #2
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sounds over-engineered to me

    as i understand it - generally we try to "normalise" data in our db systems - ie that fact that you have dupicated data seems against this principle

    I believe that mysql db can get quite big indeed, performance is probably more a function of structure, indexes and query design than size - you can return reasonable number of rows from a large appropriately indexed db quite quickly - it takes time to return large numbers of rows. Generally in a web system, you only want to return a number of rows suitable for displaying on one page, so with appropriate design and optimization, this should be possible within a reasonable time most problem spaces

  3. #3
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    notabot, thanks for your reply!

    Yes, I agree that the design now is a bit inflexible, even though you can do cross-database JOINs (but that requires user privileges I can't set with my current host), so one centralised database seems to be the best idea. It frees up the other databases for seperate projects, too.

    Besides, trial and error is the way I've learned my PHP/Java over the past few years, I'll try having one database and see what happens, eh?


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
  •