Forum Moderators: coopster
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 ...
And back at you: Happy New Year! (It's going to be a good one.)
Yeah, I'm thinking that I'm being a bit silly with the databases. It's all very well to seperate content, but I think it's unnecessary, the way I've done it here.
Besides, if my host offers six MySQLs, I should really stick to one per domain or something. Since apparently storage isn't an issue! Perfomance is though..
Thanks Salsa! Anyone else?
[edited]Ah, I see that you edited your last post, where you gave me the cheesy grin about 8 million terabytes![/edited]
jollymcfats - I've tried this before, it didn't work for some reason. Can't remember why. But if you're JOIN-ing data (and incurring the overhead), you might as well have it all in one database anyway.. right?
The number of databases involved doesn't matter in the performance of a MySQL join, though going with fewer databases is simpler- and that may be beneficial in the long run.
In terms of general overhead, I'd think that a typical join query on properly indexed tables would be basically instantaneous compared to the overhead of making 2 seperate database connections and issuing two separate queries. Connection setup and query preparation are relatively expensive.
Having just ONE database connection per page would probably be a good thing though.
