First of all, I know very little about the mechanics of MySQL server administration and was wondering if, “conueries” being equal, there is any difference to the server if they are directed toward a single database or several.
I guess another way of putting it would be:
Is it “gentler” on DH servers to create a lot of small databases (making them specific to a given application for instance) or to utililze table prefixes to run mutiple instances of an application within a single, and ultimately rather large, database (a holdover from the “good old days” when DH charged per database).
My sense of it is that, especially after waiting for phpMyAdmin to load up that massive “old style” single database with several hundred tables, that the smaller databases are eaiser on the server but, given that migrating the data from the large database to a series of smaller ones is a considerable amount of work, I wanted to get some input from the “old hats”.
Both methods have drawback/advantages for me, for example:
On the “large” database, it’s a bit of a pain to build queries to drop multiple tables (no “wildcarding”, ratz!) when deleting an application in the big database, and nultiple applications using common table names can be an issue but the fact that a user cannot access more than one database requires managing a slew of users to use a lot of little databases.
On the large database model, backing up huge amounts of contents is a breeze (all the data from multiple applications in one hughe dump, yeah!) but deleting/upgrading migrating individual applications data is much easier with a small “application specific” database, and the common table name issue is nonexistant.
For me, given either method has it’s advantages/disadvantages I would probably choose to use the method that it was kinder to the server and hence the shared hosting community, YMMV.
Thanks in advance for any input on this subject; I know this is probably trivial and your time and expertise is sincerely appreciated.