Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks for the answer. It's amazing to fathom that someone could actually have 42 billion entries in a table... and what kind of hardware is required to query a table of that magnitude. I'm still in the minors and work as a computer tech, coder, ... I suppose a general I.T. employee for a small corporation, but I am interested in developing enterprise applications. –  Mechaflash Nov 29 '11 at 17:43
2  
I used to run a system with one particular table of over 4.5 billion rows, and depending on the filtering of the query, if it matched the index nicely, it would return results in < 10 seconds at times. The table was 200Gb (inc indexes), and ran on an Intel dual quad core with 64Gb ram, and the data was on a 14 disk RAID 10 array... so it is doable!!! :) –  Dave Rix Nov 29 '11 at 21:50
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.