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!
+3
A:
mysql int types can do quite a few rows: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
unsigned int largest value is 4,294,967,295
unsigned bigint largest value is 18,446,744,073,709,551,615
KM
2010-04-26 19:30:04
2147483647 max, so you only have to make autoincrement bigint if you're working with multiple billions of entries? (which would probably just make your select statements melt down long before then)
Tchalvak
2010-04-26 19:35:13
+2
A:
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.
As far as I have read, there's no architectural limit to the number of rows per table in MySQL.
Re your comment: I need to correct my information with this link:
http://dev.mysql.com/doc/refman/5.1/en/configure-options.html#option_configure_with-big-tables
The MyISAM storage engine supports 232 rows per table, but you can build MySQL with the --with-big-tables option to make it support up to 264 rows per table.
http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html
The InnoDB storage engine doesn't seem to have a limit on the number of rows, but it has a limit on table size of 64 terrabytes. How many rows fits into this depends on the size of each row.
Bill Karwin
2010-04-26 20:00:52