Maximum Number of Tables in MySQL

Question

What is the maximum number of tables that MySQL can handle?

Answer 1

The fact that you are asking this question is probably an indicator that you are not taking a best practice approach to your problem.

You may want to explain why you need to create many tables, in order to see if you will receive better suggestions on how to tackle this issue.

However, there are no server limits on the number of tables in a MySQL database, but since each MyISAM table has associated files, then any OS limits on the number of files allowed will have an effect. InnoDB tables, indexes, etc, are stored in a single tablespace, with a maximum of two billion tables. (Source)

Answer 2

I also have a use case where a large number of tables is needed. At the moment one of our databases has close to 100.000 tables with excellent performance.

As Rob Guinness mentioned, there is a significant overhead for the .frm files. It's also important to note that any queries into information_schema without limitations on the schema and if possible the table itself will result in a serious performance hit while the query runs.