| Working with Tables | ||||||||||||||
| MySQL Table Types | ||||||||||||||
| MySQL supports multiple table handlers, each of which implements a table type that has a specific set of properties or characteristics. The table types actually available to you will depend on your version of MySQL, how it was configured at build time, and the options with which it was started. | ||||||||||||||
| The current table type handlers and the versions in which they are first available are listed in the following table: | ||||||||||||||
|
||||||||||||||
| Two version numbers are listed for BDB and InnoDB. The first number indicates when the table type appeared in binary distributions, the second when it became available in source distributions. MRG_MyISAM and BerkeleyDB are synonyms for MERGE and BDB. (From 3.23.29 through 3.23.36, the InnoDB table type was known as Innobase; thereafter, InnoDB is the preferred name, though Innobase is recognized as a synonym.) | ||||||||||||||
| Because MySQL can be configured in different ways, it's quite possible that a server for a given version of MySQL will not support all table types available in that version. The general characteristics of MySQL's table types are described below: | ||||||||||||||
| ISAM Tables | ||||||||||||||
| The ISAM handler manages tables that use the indexed sequential access method. The ISAM storage format is the original MySQL table type and is the only one available prior to Version 3.23. The ISAM handler has since been superceded by the MyISAM handler; MyISAM tables are the preferred general replacement because they have fewer limitations. | ||||||||||||||
| The ISAM type is still available but is considered pretty much obsolete. Support for it will fade over time. (ISAM table support has been omitted from the embedded server now, for example, and probably will disappear entirely in MySQL 5.) | ||||||||||||||
| MyISAM Tables | ||||||||||||||
| The MyISAM storage format is the default table type in MySQL as of version 3.23, unless the server has been configured otherwise. | ||||||||||||||
| . Tables can be larger than for the ISAM storage method if your operating system itself allows large file sizes. | ||||||||||||||
| . Table contents are stored in machine-independent format. This means you can copy tables directly from one machine to another, even if they have different architectures. | ||||||||||||||
| . Relative to ISAM tables, MyISAM relaxes several indexing constraints. | ||||||||||||||
| . MyISAM format provides better key compression than does ISAM format. Both formats use compression when storing runs of successive similar string index values, but MyISAM also can compress runs of similar numeric index values because numeric values are stored with the high byte first. (Index values tend to vary faster in the low-order bytes, so high-order bytes are more subject to compression.) To enable numeric compression, use the PACK_KEYS=1 option when creating a table. | ||||||||||||||
| . MyISAM has more capable AUTO_INCREMENT handling than is available for other table types. | ||||||||||||||
| . For improved table integrity checking, each MyISAM table has a flag that is set when the table is checked by the server or by myisamchk. MyISAM tables also have a flag indicating whether a table was closed properly. If the server shuts down abnormally or the machine crashes, the flag can be used to detect tables that need to be checked. This can be done automatically at server startup time by specifying the --myisam-recover option. | ||||||||||||||
| . The MyISAM handler supports full text searching through the use of FULLTEXT indexes. | ||||||||||||||
| MERGE Tables | ||||||||||||||
| MERGE tables are a means for grouping multiple MyISAM tables into a single logical unit. By querying a MERGE table, you are in effect querying all the constituent tables. One advantage of this is that you can in effect exceed the maximum table size allowed by the file system for individual MyISAM tables. | ||||||||||||||
| The tables that make up a MERGE table must all have the same structure. This means the columns in each table must be defined with the same names and types in the same order, and the indexes must be defined in the same way and in the same order. It is allowable to mix compressed and uncompressed tables. | ||||||||||||||
| A MERGE table cannot refer to tables in a different database. | ||||||||||||||
| HEAP Tables | ||||||||||||||
| The HEAP storage format uses tables that are stored in memory and that have fixed-length rows, two characteristics that make them very fast. HEAP tables are temporary in the sense that they disappear when the server terminates. | ||||||||||||||
| However, in contrast to temporary tables created with CREATE TEMPORARY TABLE, HEAP tables are visible to other clients. Several constraints apply to HEAP tables that allow them to be handled more simply and thus more quickly: | ||||||||||||||
| . Indexes are used only for comparisons performed with the = and <=> operators. This is due to the use of hashed indexes, which are very fast for equality comparisons but not for range searches with comparison operators such as < or >. Indexes also are not used in ORDER BY clauses for this reason. | ||||||||||||||
| . You cannot have NULL values in indexed columns prior to MySQL 4.0.2. | ||||||||||||||
| . AUTO_INCREMENT columns cannot be used prior to MySQL 4.1. | ||||||||||||||
| . BLOB and TEXT columns cannot be used. Because rows are stored using fixed-length format, you cannot use variable length column types such as BLOB and TEXT. VARCHAR is allowed but is treated internally as the corresponding CHAR type. | ||||||||||||||
| BDB Tables | ||||||||||||||
| BDB tables are managed by the Berkeley DB handler developed by Sleepycat. The BDB handler offers these features: | ||||||||||||||
| . Transaction-safe tables with commit and rollback
. Automatic recovery after a crash . Page-level locking for good concurrency performance under query mix conditions that include both retrievals and updates |
||||||||||||||
| InnoDB Tables | ||||||||||||||
| InnoDB tables are the most recent table type added to MySQL. They are managed by the InnoDB handler developed by Innobase Oy. The InnoDB handler offers the following features: | ||||||||||||||
| . Transaction-safe tables with commit and rollback.
. Automatic recovery after a crash. . Foreign key support, including cascaded delete. |
||||||||||||||
| . Row-level locking for good concurrency performance under query mix conditions that include both retrievals and updates. | ||||||||||||||
| . InnoDB tables are managed within a separate tablespace rather than by using table-specific files like the other table types. The tablespace can consist of multiple files and can include raw partitions. The InnoDB handler, in effect, treats the tablespace as a virtual file system within which it manages the contents of all InnoDB tables. | ||||||||||||||
| . Tables can exceed the size allowed by the file system for individual files through use of multiple files or raw partitions in the tablespace. | ||||||||||||||
|
||||||||||||||
