MySQL doubt: More DB's & less tables || More tables less DB's
Hi, im planning on designing a webservice, it will have a lot of data, spread in many tables.
The problem is that there will be tables created constantly (around 5 each day..)
All tables/data will be accessed equally, so I dont know how to set up the system, to create multiple databases, and balance the number of tables equally on each database, or create less databases and have a lot of tables on each one.
Which one has more efficiency?
Your input is appreciated!
If you are designing this from a single appliacation, it would be better to use 1 DB and have many tables. Creating a connection to a database is an expensive operation and you want to minimize that. If you can, use a connection pool to be able to reuse connections.
As far as mysql is concerned the application will perform roughly the same either way.
However, the host OS may work better with a balance between the number of databases/tables per database.
If you have a truly massive number of tables you may run into poor file system performance due to too many files in one folder if you put them in the same database. It also means its more difficult to mount them on different drive spindles .