MySQL load

several databases or single db

[topr8]

have been searching around the web, but info on performance is always hard to come by (countless tutorials on how to, but not many whys!).

now with an install of mySQL you can have multiple databases, but i'm assuming that the same mySQL engine drives the whole lot if that is a correct analogy.

so in terms of performance i'm assuming that there is no difference between running one mega database over several smaller ones (which add up to the same as the mega) - the scenario is multiple websites, i could use one seperate db for each site or one db for the whole lot, from an admin point of view one for the whole lot makes a lot of sense.

any thoughts.

to give an idea of scale i'm talking a few tables with 10,000's of records rather than million+, also some logging tables could run into the 100,000s of rows but these would only be write new records to table commands (the logging analysis/crunching would be done elsewhere combined with the apache logfiles)

most queries run would be relatively straightforward join queries.

[mcavic]

I believe that the number of tables and rows is what matters, not the number of databases.

If the web sites need to share data, then I'd use one database. If they don't, and you want extra security, I'd use more than one.

[topr8]

thanks, but why would seperate db's be extra security?

[mcavic]

Because you can give them different usernames and passwords, and if one of them is hacked, the others may still be safe.

[jollymcfats]

mcavic is correct, the number of databases doesn't really factor into performance, especially on this very small scale.

"Databases" in MySQL are mostly just a convienent way to keep data separated, kind of like folders in a file system. Like filesystem folders and files, you can apply access control at the database level and/or at the table level.

Slashdot is running a book review today for a book supposedly heavy on the why of MySQL performance. The book is called High Performance MySQL.