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

User · Answer

I suggest  never delete data  Don t say if the tables is longer than 1000 truncate the end of the table  There needs to be real business logic in your plan like how long has this user been inactive  For example  if it is longer than 1 year then put them in a different table  You would have this happen weekly or monthly in a maintenance script in the middle of a slow time   When you run into to many rows in your table then you should start sharding the tables or partitioning and put old data in old tables by year such as users 2011 jan  users 2011 feb or use numbers for the month  Then change your programming to work with this model  Maybe make a new table with less information to summarize the data in less columns and then only refer to the bigger partitioned tables when you need more information such as when the user is viewing their profile  All of this should be considered very carefully so in the future it isn t too expensive to re-factor  You could also put only the users which comes to your site all the time in one table and the users that never come in an archived set of tables

User · Answer

In InnoDB  with a limit on table size of 64 terabytes and a MySQL row-size limit of 65 535 there can be 1 073 741 824 rows  That would be minimum number of records utilizing maximum row-size limit  However  more records can be added if the row size is smaller

User · Answer

According to Scalability and Limits section in http   dev mysql com doc refman 5 6 en features html  MySQL support for large databases  They use MySQL Server with databases that contain 50 million records  Some users use MySQL Server with 200 000 tables and about 5 000 000 000 rows

User · Answer

Link http   dev mysql com doc refman 5 7 en column-count-limit html  Row Size Limits  The maximum row size for a given table is determined by several factors   The internal representation of a MySQL table has a maximum row size limit of 65 535 bytes  even if the storage engine is capable of supporting larger rows  BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row   The maximum row size for an InnoDB table  which applies to data stored locally within a database page  is slightly less than half a page for 4KB  8KB  16KB  and 32KB innodb page size settings  For example  the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size  For 64KB pages  the maximum row size is slightly less than 16KB  See Section 15 8 8     Limits on InnoDB Tables      If a row containing variable-length columns exceeds the InnoDB maximum row size  InnoDB selects variable-length columns for external off-page storage until the row fits within the InnoDB row size limit  The amount of data stored locally for variable-length columns that are stored off-page differs by row format  For more information  see Section 15 11     InnoDB Row Storage and Row Formats      Different storage formats use different amounts of page header and trailer data  which affects the amount of storage available for rows   For information about InnoDB row formats  see Section 15 11     InnoDB Row Storage and Row Formats     and Section 15 8 3     Physical Row Structure of InnoDB Tables      For information about MyISAM storage formats  see Section 16 2 3     MyISAM Table Storage Formats      http   dev mysql com doc refman 5 7 en innodb-restrictions html

User · Answer

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 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 has an internal 6-byte row ID per table  so there are a maximum number of rows equal to 248 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

User · Answer

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

User · Answer

Row Size Limits   The maximum row size for a given table is determined by several factors      The internal representation of a MySQL table has a maximum row size limit of 65 535 bytes  even if the storage engine is capable of supporting larger rows  BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row  The maximum row size for an InnoDB table  which applies to data stored locally within a database page  is slightly less than half a page  For example  the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size  which is defined by the innodb page size configuration option     Limits on  InnoDB Tables     If a row containing variable-length columns exceeds the InnoDB maximum row size  InnoDB selects variable-length columns for external off-page storage until the row fits within the InnoDB row size limit  The amount of data stored locally for variable-length columns that are stored off-page differs by row format  For more information  see    InnoDB Row Storage and Row Formats     Different storage formats use different amounts of page header and trailer data  which affects the amount of storage available for rows

User · Answer

There is no limit  It only depends on your free memory and system maximum file size  But that doesn t mean you shouldn t take precautionary measure in tackling memory usage in your database  Always create a script that can delete rows that are out of use or that will keep total no of rows within a particular figure  say a thousand

[mysql] Maximum number of records in a MySQL database table

Examples related to mysql

Examples related to database

Examples related to limit

Examples related to database-table