Multiple Databases?

[RogueTomboy]

I was chatting and something came to mind about databases...

Which is a better database set up?

OPTION 1
Database (insert your name here)
-ALL your tables go here

or...

OPTION 2
Database 1 (insert name here)
-this db only holds player register info/playercharacter/guilds tables
Database 2 (insert name here)
-this db holds all the equipment/armor/weapons/items tables
Database 3 (insert name here)
-this db holds all NPC's (like those who sell things) and all other NPC's tables
Database 4 (insert name here)
-this db holds all your quests

You can see the pattern in option2 and then just use joins if you need to pull or insert info into the different db's?

I wasn't sure and was wondering what the best way to handle that was in reference to a game running smoothly with lesser call times.

[Loopy]

Can't really think of a good reason for a BBG to use option 2, and I can't see how you'd see a noticeable performance increase unless your game was absolutely massive.

[Jackolantern]

Option 1 is probably better just from an organization standpoint. It would be difficult to keep up with the connections of many different databases. However, there wouldn't be a huge performance difference. Whatever trivial gains may be earned by having only a single table per database (as opposes to the 10 or 12 at max with option 1) would likely be lost from all of the extra connection logic.

[RogueTomboy]

Ah okay, thanks guys!

[Nexus]

The second option seems way to complicated because you would have to somehow switch which DB constantly and that would get quite annoying after some time. So I say go with option 1

[Xaleph]

From a speed point, the second will probably be way faster, but it will complicate matters. Mostly due to synchronisation between the databases, you always have to keep them synced, which will be tricky. Or, wait, that doesn`t even matter that much, we`re ralking databases in PHP right? From an organisational point of view, it wouldn`t really matter that much, scalability is all dependent on how you order tables and databases.

However, scaling the first is probably way easier. And easier means less time which in turn means less cost and maintenance. Either way, i`d stick with 1 database for now. You can always scale to bigger or blunty: more databases.

Oh on the speed thing: You can run <n> ammounts of queries to <n> ammount of databases, so you can asynchronously call upon different databases. MySQL only runs in 1 thread for each active connection ( due to PHP ) so you can see why having more then 1 database will speed things up. Connection logic isn`t that hard, creating 4 connection objects instead of 1 is neglectable really. Especially with no constructor on the con. object, just let it inherit functionality from a shared db object.

[Jackolantern]

Doesn't connecting to the db take a non-trivial amount of time, though (from a CPU perspective, of course lol.)? I have heard from several sources that connecting to a database takes longer than many types of queries.

Regardless, it may be worth it in very high traffic environments, which of course, few of us here are dealing with anywhere outside of work. For most of our purposes, the ease of having everything under 1 database is going to be the way to go.

[Xaleph]

Connecting to a database? That`s the equivalent of running a query. It goes in, it comes out. All it does is flagging a simple boolean, most of the times, MySQL anyway. From that point forward, every time you execute a query, PHP goes diving in, retrieves the connection and verifies the flag.

The main reason why most websites run on 1 db model is mostly because it scales so easy. All you need to do is set up a new database somewhere, enter the credentials, host, port et cetera, and you have a working database.

The other reason why most websites run on 1 db is that 1 db usually suffices. Most traffic takes place on the server itself, not in the DB. Even high traffic sites don`t query so much. Most of the resultsets are stored in cache files, or complete pages to be returned. Actually querying is only done is searching, and even that can be cached.

But try it yourself, create 4 databases, create some db objects, asynchronously query something for a a 1000 times ( running 4k total ) and check the time it took. Now, do the same thing running 4k queries on 1 db. You`ll see the difference.