How Big MySQL Tables Can Be

MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the MyISAM storage engine in MySQL 3.23, the maximum table size was increased to 8 million terabytes (2 ^ 63 bytes). With this larger allowed table size, the maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits.
The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64TB.
The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.

Operating System	File-size Limit
Linux 2.2-Intel 32-bit	2GB (LFS: 4GB)
Linux 2.4	(using ext3 filesystem) 4TB
Solaris 9/10	16TB
NetWare w/NSS filesystem	8TB
win32 w/ FAT/FAT32	2GB/4GB
win32 w/ NTFS	2TB (possibly larger)
MacOS X w/ HFS+	2TB

On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the Large File Support (LFS) patch for the ext2 filesystem. On Linux 2.4, patches also exist for ReiserFS to get support for big files (up to 2TB). Most current Linux distributions are based on kernel 2.4 and include all the required LFS patches. With JFS and XFS, petabyte and larger files are possible on Linux. However, the maximum available file size still depends on several factors, one of them being the filesystem used to store MySQL tables.
For a detailed overview about LFS in Linux, have a look at Andreas Jaeger's Large File Support in Linux page at http://www.suse.de/~aj/linux_lfs.html.
Windows users please note: FAT and VFAT (FAT32) are not considered suitable for production use with MySQL. Use NTFS instead.
By default, MySQL creates MyISAM tables with an internal structure that allows a maximum size of about 4GB. You can check the maximum table size for a table with the SHOW TABLE STATUS statement or with myisamchk -dv tbl_name. See Section 13.5.4, “SHOW Syntax”.
If you need a MyISAM table that is larger than 4GB in size (and your operating system supports large files), the CREATE TABLE statement allows AVG_ROW_LENGTH and MAX_ROWS options. See Section 13.2.6, “CREATE TABLE Syntax”. You can also change these options with ALTER TABLE after the table has been created, to increase the table's maximum allowable size. See Section 13.2.2, “ALTER TABLE Syntax”.
Other ways to work around file-size limits for MyISAM tables are as follows:

·        If your large table is read-only, you can use myisampack to compress it. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. myisampack also can merge multiple tables into a single table. See Section 8.2, “myisampack, the MySQL Compressed Read-only Table Generator”.

·        Another way to get around the operating system file limit for MyISAM data files is by using the RAID options. See Section 13.2.6, “CREATE TABLE Syntax”.

·        MySQL includes a MERGE library that allows you to handle a collection of MyISAM tables that have identical structure as a single MERGE table. See Section 14.2, “The MERGE Storage Engine”.

 

13.2.4. CREATE DATABASE Syntax

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

    [create_specification [, create_specification] ...]

create_specification:

    [DEFAULT] CHARACTER SET charset_name

  | [DEFAULT] COLLATE collation_name

CREATE DATABASE creates a database with the given name. To use CREATE DATABASE, you need the CREATE privilege on the database.
Rules for allowable database names are given in Section 9.2, “Database, Table, Index, Column, and Alias Names”. An error occurs if the database exists and you didn't specify IF NOT EXISTS.
As of MySQL 4.1.1, create_specification options can be given to specify database characteristics. Database characteristics are stored in the db.opt file in the database directory. The CHARACTER SET clause specifies the default database character set. The COLLATE clause specifies the default database collation. Character set and collation names are discussed in Chapter 10, Character Set Support.
Databases in MySQL are implemented as directories containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the CREATE DATABASE statement only creates a directory under the MySQL data directory (and the db.opt file, for MySQL 4.1.1 and up).
If you manually create a directory under the data directory (for example, with mkdir), the server considers it a database directory and it shows up in the output of SHOW DATABASES.
CREATE SCHEMA can be used as of MySQL 5.0.2.
You can also use the mysqladmin program to create databases. See Section 8.4, “mysqladmin, Administering a MySQL Server”.

13.2.8. DROP DATABASE Syntax

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE drops all tables in the database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the DROP privilege on the database.
In MySQL 3.22 or later, you can use the keywords IF EXISTS to prevent an error from occurring if the database doesn't exist.
DROP SCHEMA can be used as of MySQL 5.0.2.
If you use DROP DATABASE on a symbolically linked database, both the link and the original database are deleted.
As of MySQL 4.1.2, DROP DATABASE returns the number of tables that were removed. This corresponds to the number of .frm files removed.
The DROP DATABASE statement removes from the given database directory those files and directories that MySQL itself may create during normal operation:

·        All files with these extensions:

.BAK	.DAT	.HSH	.ISD
.ISM	.ISM	.MRG	.MYD
.MYI	.db	.frm

·        All subdirectories with names that consist of two hex digits 00-ff. These are subdirectories used for RAID tables.

·        The db.opt file, if it exists.

If other files or directories remain in the database directory after MySQL removes those just listed, the database directory cannot be removed. In this case, you must remove any remaining files or directories manually and issue the DROP DATABASE statement again.
You can also drop databases with mysqladmin. See Section 8.4, “mysqladmin, Administering a MySQL Server”.