9.3 How big MySQL tables can be

MySQL 3.22 has a 4G limit on table size. With the new MyISAM in MySQL 3.23 the maximum table size is pushed up to 8 million terabytes (2 ^ 63 bytes).

Note however that operating systems have their own file size limits. On Linux, the current limit is 2G; on Solaris 2.5.1, the limit is 4G; on Solaris 2.6, the limit is 1000G. This means that the table size for MySQL is normally limited by the operating system.

By default, MySQL tables have a maximum size of about 4G. You can check the maximum table size for a table with the SHOW TABLE STATUS command or with the myisamchk -dv table_name. 7.20 SHOW syntax (Get information about tables, columns,...).

If you need bigger tables than 4G (and your operating system supports this), you should set the AVG_ROW_LENGTH and MAX_ROWS parameter when you create your table. 7.6 CREATE TABLE syntax. You can also set these later with ALTER TABLE. 7.7 ALTER TABLE syntax.

If your big table is going to be read-only, you could use pack_isam to merge and compress many tables to one. pack_isam usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. pack_isam.

Another solution can be the included MERGE library, which allows you to handle a collection of identical tables as one. (Identical in this case means that all tables are created with identical column information.) Currently MERGE can only be used to scan a collection of tables because it doesn't support indexes. We will add indexes to this in the near future.