Have too many tables in a Mysql database can affect performance?

Having too many tables (200 for example) in a single Mysql database instance can reduce it performance?

Solution 1:

In general, the more of anything you have will reduce performance. However, 200 seems like a fairly small number. 2,000 might be a real performance hit and definitely 20,000. In general, though, you should keep your number of tables small since MySQL can handle very large number of rows in the table.

Solution 2:

In general 200 tables should not be an issue but it depends on a number of things.

Dedicated MySQL server vs Server shared with other software, 128Mb vs 128Gb RAM etc., small tables with few records vs tables with blobs and millions of rows.

MySQL has may settings and multiple engines each effecting tables performance in different ways.

MyISAM normally has three files per table .frm, .MYD, .MYI

INNODB in normal mode has .frm with all the data stored in the central file

INNODB in one file per table mode has .frm, .idb

table_open_cache is the number of tables that can be open at once (Default 64). This may need to be larger than the number of tables in you schema as it is related to how many connections are querying the DB. 100 connections join 3 tables could mean you have 300 tables cached plus any temporary tables. Generally more complex a schema or connections the bigger the number.

open-files-limit I tend to set this to 4x table_open_cache which should be generous rather than bothering to work out an exact value.

The operating systems file handle limit for the mysql user may also be an issue (on linux the default can often be 1024, ulimit -n to show user limits) this can cause an issue with large numbers of tables when it is less that the number mysql requires. This should be at least the same as the open-files-limit.

As with any database there are hundreds of tuning parameters that you can adjust to optimise the database for your particular schema. MySQL is worse for this than most as you can plug in extra engines if you want i.e. NDB cluster engine.

http://dev.mysql.com/doc/refman/5.1/en/table-cache.html

Hope it helps

Solution 3:

The number of tables doesn't matter so much as:

1. What queries you're running - you aren't likely to be querying all 200 within a single query
2. The overall query load on the system at any given point in time

Having excess tables does mean memory & hard drive space that could be regained & used for other things. Keep in mind that denormalizing your tables increases the risk of bad data because you are getting rid of referencial integrity.