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!