Hosting: why does the number of MySQL databases matter?

Question

Ok, maybe I'm missing something here but I'm looking at various PHP hosting options and I see things like "10 MySQL databases", or 25 or even unlimited.

Now I've worked on sites with an Oracle backend that have 10,000+ concurrent users and we've had... one database.

The idea of a database is, of course, that you can store whatever you want in it. So why is it for MySQL that the number matters? Is there some table, row or overall database limit I'm not aware of (entirely possible)? Or is it a question or concurrent connections? Or some other performance issue (eg sharding)? The sharding aspect seems unlikely because even basic hosting options (ie under $5/month) I see with 10 databases.

If someone could clue me in on this one, it'd be great.

Answer 1

Some people like one database per app

Answer 2

It's marketing, not technical. They want something to advertise. "10" sounds like a good number.

Answer 3

When I used shared hosting, I set up a separate database for each site/client for custom apps, and if you use Fantastico to install applications it will use a database for each one by default.

I believe the limits are there to prompt you to upgrade to the next tier of service when you outgrow the current level.

Answer 4

For development purposes, sometimes it's good to make a copy of your entire database to test new software against. Beats renaming all the tables in your code (although apps like Wordpress let you specify a prefix for all your table names in case you don't have the luxury of multiple DBs).

Answer 5

It's mostly a marketing tactic, although there are some technical and historical considerations.

First, apologies if this is obvious, but SCHEMAs are to Oracle as DATABASES are to MySQL (in over simplified terms, a logical collections of tables).

The host is saying you can have XX number of configured logical databases on a server. Lots of web applications need a database to run. Modern web applications like Wordpress, Movable Type, Joomla, etc., will let you name your tables with a custom prefix. However, if an application doesn't have this configuration feature that means you need one database per install. Also, in a similar vein, if two applications have the same table name, they can't coexist in a single database. Lots of early web applications started out like this, so early on number of databases was an important feature to consider.

There's also access and security. While MySQL (and other databases) can be configured to give users fine grained access-control down to the table and column level, it's often easier to create one user who has full permission on a logical Database. This is important to people who sell services but pass off the actual hosting of completed sites/applications to the shared web-host.

Answer 6

Additionally its possibly a marketing perspective.

If you're only setting up databases for yourself, the low count is fine. but for commerical users, whom may want to have multiple sites for multiple clients on the one service, trying to cut corners, you're likely to need 1 Database ( or more ) per client/project.

So putting a limit on number of databases controls somewhat the variety services you offer, and potentially limits potential for your "resale" value, ie: to stop you buying 1 plan and then selling it on to somebody else, like "subleasing".

Answer 7

Nick is partially correct, but it also has to do with people who will try to host multiple sites on one shared account and will use a different database for each and a script to serve the correct content with a little dns masquerading.

Answer 8

This is mainly for when you are hosting multiple sites on the same box. For me, I buy/sell a lot websites so I need to be able to keep each website as detached from the others as possible.