MySQL database speed question

Question

Is it all right to have many tables inside a database or should I create another database? If so, what is the limit on how many tables I should have in a database?

Will having many tables in a database affect the speed of the database?

Answer 1

I don't believe it makes much difference in performance if your multitude of tables is in one or several databases. (If you need to reference multiple tables in one query then they will be a bit easier to write the queries if the tables are not all in different databases, though that's not a performance matter.)

Implied in your question, however, is another question: What are the performance effects of structuring my data into specifically these tables as opposed to some other schema that uses a different number and structure of tables. That's a question many DB designers face. There often are significant performance differences. There are few general rules with any validity. One that's included in the MySQL manual is: Use the schema that minimizes total DB size. It's not guaranteed to be the most performant schema but it's often one worth considering.

Answer 2

Really, the key is to employ a good relational database design, and understand and optimize your queries appropriately. Having many tables in a database won't affect the speed. Building those tables with bad design, and accessing data with inefficient queries absolutely will.

One limitation in MySQL to be aware of is that a single table cannot be over 4GB in size using the MyISAM database engine. InnoDB does not have that limitation that I'm aware of.

Answer 3

It depends on the size of the tables, as well as the amount of reading / writing you're going to be doing, which again depends on the hardware you're running, and the types of tables you're using.

Performance is usually reduced by lots of I/O because that tends to be the slowest part of a system.

As for your other question about limits, may I suggest having a look through the MySQL documentation.