Now it’s time to look at each of MySQL’s storage engines in more detail. Table 2-3 summarizes some of the high-level characteristics of the handlers. The following sections provide some basic highlights and background about each table handler as well as any unusual characteristics and interesting features.
Before going further, it’s worth noting that this isn’t an exhaustive discussion of MySQL’s storage engines. We assume that you’ve read (or at least know where to find) the information in the MySQL Reference Manual.
Table 2-5: Storage Engine features in MySQL
*Prior to MySQL 4.0, InnoDB was available in MySQL-Max only.
Most of MySQL’s disk-based tables have some basic things in common. Each database in MySQL is simply a subdirectory of MySQL’s data directory in the underlying filesystem.* Whenever you create a table, MySQL stores the table definition in a .frm file with the same name as the table. Thus, when you create a table named MyTable, MySQL stores the table definition in MyTable.frm.
* In MySQL 5.0, the term “database” will likely morph into “schema.”
To determine the type of a table, use the SHOW TABLE STATUS command. For example, to examine the user table in the mysql database, you execute the following:mysql> SHOW TABLE STATUS LIKE 'user' \G
****************** 1. row ***************************
Notice that it’s a MyISAM table. You might also notice a lot of other information and statistics in the output. Let’s briefly look at what each line means:
The table’s name.
The table’s type. Again, in some versions of MySQL, this may say “Engine” rather than “Type.”
Dynamic, Fixed, or Compressed. Dynamic rows vary in length because they contain variable-length fields such as VARCHAR or BLOB. Fixed rows, which are always the same size, are made up of fields that don’t vary in length, such as CHAR and INTEGER. Compressed rows exist only in compressed tables (see the later section “Compressed MyISAM”).
The number of rows in the table. For non-transactional tables, this number is always accurate. For transactional tables, it is usually an estimate.
How many bytes the average row contains.
How much data (in bytes) the entire table contains.
The maximum amount of data this table can hold. In a MyISAM table with dynamic (variable length) rows, the index file for a table (tablename.MYI) stores row locations using 32-bit pointers into the data file (tablename.MYD). That means it can address only up to 4 GB of space by default. See the next section, “MyISAM Tables” for more details. For MyISAM tables with fixed-length rows, the limit is just under 4.3 billion rows.
How much space is consumed by index data.
The amount of space that has been allocated but is currently unused.
The next AUTO_INCREMENT value.
When the table was first created.
When data in the table last changed.
When the table was last checked using CHECK TABLE or myisamchk.
Any other options that were specified when the table was created.
The comments, if any, that were set when the table was created.
blog comments powered by Disqus