Is there a max allowance for entries in a table in mysql?

Question

Is there a set maximum (or even a recommended maximum) for how many entries that can/should be stored in a mysql table? How much is really too much?

Answer 1

There is a way to set the maximum number of rows on a table for MyISAM.

According to the MySQL Documentation under AVG_ROW_LENGTH:

When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table is. If you don't specify either option, the maximum size for MyISAM data and index files is 256TB by default. (If your operating system does not support files that large, table sizes are constrained by the file size limit.) If you want to keep down the pointer sizes to make the index smaller and faster and you don't really need big files, you can decrease the default pointer size by setting the myisam_data_pointer_size system variable. (See Section 5.1.3, “Server System Variables”.) If you want all your tables to be able to grow above the default limit and are willing to have your tables slightly slower and larger than necessary, you can increase the default pointer size by setting this variable. Setting the value to 7 permits table sizes up to 65,536TB.

According to the MySQL Documentation under MAX_ROWS:

MAX_ROWS The maximum number of rows you plan to store in the table. This is not a hard limit, but rather a hint to the storage engine that the table must be able to store at least this many rows.

The NDB storage engine treats this value as a maxmimum. If you plan to create very large MySQL Cluster tables (containing millions of rows), you should use this option to insure that NDB allocates sufficient number of index slots in the hash table used for storing hashes of the table's primary keys by setting MAX_ROWS = 2 * rows, where rows is the number of rows that you expect to insert into the table.

Note This option was incorrectly ignored by MySQL Cluster NDB 7.0 prior to version 7.0.20 and MySQL Cluster NDB 7.1 prior to version 7.1.9 (see Bug #57360).

The maximum MAX_ROWS value is 4294967295; larger values are truncated to this limit.

In terms of the number of columns, InnoDB cannot support more than 1000 columns, while MyISAM can support more.

For further comparison, please read the MySQL Documentation on MyISAM and InnoDB for their limits and options to change any configurable limits.

Answer 2

I took a look at MySQL documentation and there seems to be no maximum number for records in a table, but taking a look at MySQL features page:

Scalability and Limits:

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.

Support for up to 64 indexes per table (32 before MySQL 4.1.2). Each index may consist of 1 to 16 columns or parts of columns. The maximum index width is 1000 bytes (767 for InnoDB); before MySQL 4.1.2, the limit is 500 bytes. An index may use a prefix of a column for CHAR, VARCHAR, BLOB, or TEXT column types.

According to this information, the recommended maximum number of records may vary a lot depending on your hardware limitations but would probably be around the boundaries of 5,000,000,000 rows.

EXTRA: if you plan to store really large numbers of records in a table, care to use the MAX_ROWS parameter in your CREATE TABLE data definition statements.