1

Is it OK to keep 10000+ tables in a MySQL database? I'm making a messaging/chat script, so I'm thinking about partitioning data's over several tables as it will be a huge amount of data after some days. IS IT OK? Or it has some effect? Well, as a table can hold millions of rows so I was thinking maybe a database can hold large number of tables too

or, the question could be like, how does Facebook stores their huge amount of daily chat messages?

I'm a newbie in MySQL, please help

Ario
  • 63
  • 10
  • 2
    10000+ tables sounds like bad design for simple messaging ... i wonder how would u normalize and would remember the purpose of those tables when doing CRUD on anyother action – Digital Alchemist Aug 23 '13 at 04:15
  • Unless you really know what you are doing, you are just creating a whole lot of work for yourself with no benefit. Have a look at this answer about MySQL sharding. http://stackoverflow.com/a/5617449/20869 – bumperbox Aug 23 '13 at 04:23

2 Answers2

2

MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables.

Even so, the typical DBMS will 'handle' such large databases, but there is more strain on the system catalog than usual in such systems.

I have about huge tables in one database with no ill effects, other than displaying the table list in phpMyAdmin taking a while

Digital Alchemist
  • 2,324
  • 1
  • 15
  • 17
1

It's possible, but I would avoid it unless you have a really good use case for it. It raises all kinds of scalability and maintainability issues. Your table size is mainly limited by available disk space.

If you really need to do it...

You'll need to increase the maximum number of file descriptors that your OS will allow to have open, since MyISAM tables use two file descriptors per table. (If you're using Linux then read the section about ulimit in the man page for bash for how to do this).

Also, there's a MySQL config value called table_cache that limits the number of allowed tables. You'll need to make sure that's large enough to support the number of tables you need.

You won't want to use the standard "flush tables" anymore (unless you're the kind of person that likes to watch paint dry) so you'll need to flush each table individually (e.g. before shutdown).

Again, I would avoid using so many tables. You're probably better off making your schema support what you need in a handful of tables, and consider archiving, warehousing (or deleting!) old data if you're concerned about storing too much data.

erturne
  • 1,799
  • 1
  • 15
  • 29