Many Tables in MySQL Vs Many DBs in Mysql

[BlackRaven]

quick ques, is there any significant performance difference when using a mysql db with many tables vs a mysql db with one table? I only ask since godadddy only offers 1 db and was thinking if it would be worth upgrading to a higer number of db.

[wrightee]

If your db is very big; probably. If not (<1m rows), not much. Performance hits in MySQL come mainly from bad key optimisation, intensive searches like LIKE, and heavy insert/update/delete operations not being properly optimised.

Sep dbs are useful for backup and added security though, particularly if multiple client data is stored.

[killroy]

Always remember, each db is a folder and each table a set of files. Which means same number of files either way. I think it's more an oirganisational issue and a permissions issue.

If you're the only one using the db, I'd bet dbname.prefix_tablename has te same performance as dbname_prefix.tablename.

SN

[anshul]

You need not grow your expenses.
A good of organising 100s of tables in a single db is adding prefix to each of your tables.
e.g., you are maintaining subscription mailing lists for users using ( normalized ) 5-10 tables with prefix 'subscription_'
-do- ( etc. )

[BlackRaven]

thnx everyone, think i will just stick with my 1 db

[ergophobe]

For me and my small brain, I like to use one DB per site. That's just easy to keep track of and script for and maintain.

None of my DBs are big though - 10-12MB max.