What is maximum records quantity a MySQL table can store?

Question

How many records can a MySQL MyISAM table store? How many InnoDB can?

Solution 1

You can't count by number of records because your table can have really short records with only a few int fields or your records might be really long with hundreds of fields.

So it has to be measured in the file size of the tables.

For MYSQL: The table size limit is based on the operating system drive file system that MySQL is installed on, ranging from 2GB to 2TB.

See the MySQL reference manual for full explanations of limits for each operating system.

Concerning InnoDb and MyIsam i do not know.

Solution 2

From the MySQL site:

Support for large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 200,000 tables and about 5,000,000,000 rows.

The more practical limit will be the size of your key -- if your primary key is an int field, then the max number of rows will be the largest number that can be held in an int.

So if you're expecting a big table size, use bigint ... or something even bigger.