Maximum number of records in a MySQL database table

Question

What is the upper limit of records for MySQL database table. I'm wondering about autoincrement field. What would happen if I add milions of records? How to handle this kind of situations? Thx!

Answer 1

The greatest value of an integer has little to do with the maximum number of rows you can store in a table.

It's true that if you use an int or bigint as your primary key, you can only have as many rows as the number of unique values in the data type of your primary key, but you don't have to make your primary key an integer, you could make it a CHAR(100). You could also declare the primary key over more than one column.

There are other constraints on table size besides number of rows. For instance you could use an operating system that has a file size limitation. Or you could have a 300GB hard drive that can store only 300 million rows if each row is 1KB in size.

The limits of database size is really high:

http://dev.mysql.com/doc/refman/5.1/en/source-configuration-options.html

The MyISAM storage engine supports 2³² rows per table, but you can build MySQL with the --with-big-tables option to make it support up to 2⁶⁴ rows per table.

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

The InnoDB storage engine has an internal 6-byte row ID per table, so there are a maximum number of rows equal to 2⁴⁸ or 281,474,976,710,656.

An InnoDB tablespace also has a limit on table size of 64 terabytes. How many rows fits into this depends on the size of each row.

The 64TB limit assumes the default page size of 16KB. You can increase the page size, and therefore increase the tablespace up to 256TB. But I think you'd find other performance factors make this inadvisable long before you grow a table to that size.