Maximum number of records in a MySQL database table

by

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.

More Related Contents:

  1. How do I specify unique constraint for multiple columns in MySQL?
  2. Authentication plugin ‘caching_sha2_password’ cannot be loaded
  3. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  4. Throw an error preventing a table update in a MySQL trigger
  5. Is there a MySQL option/feature to track history of changes to records?
  6. Binary Data in MySQL [closed]
  7. Does adding ‘LIMIT 1’ to MySQL queries make them faster when you know there will only be 1 result?
  8. Using an Alias in SQL Calculations
  9. Can a table field contain a hyphen?
  10. Adding a leading zero to some values in column in MySQL
  11. Auto Generate Database Diagram MySQL [closed]
  12. View’s SELECT contains a subquery in the FROM clause
  13. MySQL : Multiple row as comma separated single row
  14. How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?
  15. Case statement in MySQL
  16. Export a MySQL Database to SQLite Database
  17. How to find out the MySQL root password
  18. Can’t connect to MySQL server on ‘127.0.0.1’ (10061) (2003)
  19. How to display open transactions in MySQL
  20. MySQL foreign key to allow NULL?
  21. MySQL query, MAX() + GROUP BY
  22. How to get multiple records against one record based on relation?
  23. Easiest way to copy a table from one database to another?
  24. How to use a user variables in MySQL LIKE clause?
  25. MySQL – Select from a list of numbers those without a counterpart in the id field of a table
  26. WooCommerce: Finding the products in database
  27. Is it unreasonable to assign a MySQL database to each user on my site?
  28. Are there any functions in MySQL like dense_rank() and row_number() like Oracle?
  29. Create a new database with MySQL Workbench
  30. Optimistic locking in MySQL

Leave a Comment