Storage Engine (Table Types) - The Storage Engines
(Page 11 of 15 )
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
Attribute | MyISAM | Heap | BDB | InnoDB |
Transactions | No | No | Yes | Yes |
Lock granularity | Table | Table | Page (8 KB) | Row |
Storage | Split files | In-memory | Single file per table | Tablespace(s) |
Isolation levels | None | None | Read committed | All |
Portable format | Yes | N/A | No | Yes |
Referential integrity | No | No | No | Yes |
Primary key with data | No | No | Yes | Yes |
MySQL caches data records | No | Yes | Yes | Yes |
Availability | All versions | All versions | MySQL-Max | All Versions* |
*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 ***************************
Name: user
Type: MyISAM
Row_format: Dynamic
Rows: 6
Avg_row_length: 59
Data_length: 356
Max_data_length: 4294967295
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2002-01-24 18:07:17
Update_time: 2002-01-24 21:56:29
Check_time: NULL
Create_options:
Comment: Users and global privileges
1 row in set (0.06 sec)
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:
Name
The table’s name.
Type
The table’s type. Again, in some versions of MySQL, this may say “Engine” rather than “Type.”
Row_format
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”).
Rows
The number of rows in the table. For non-transactional tables, this number is always accurate. For transactional tables, it is usually an estimate.
Avg_row_length
How many bytes the average row contains.
Data_length
How much data (in bytes) the entire table contains.
Max_data_length
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.
Index_length
How much space is consumed by index data.
Data_free
The amount of space that has been allocated but is currently unused.
Auto_increment
The next AUTO_INCREMENT value.
Create_time
When the table was first created.
Update_time
When data in the table last changed.
Check_time
When the table was last checked using CHECK TABLE or myisamchk.
Create_options
Any other options that were specified when the table was created.
Comment
The comments, if any, that were set when the table was created.
 | If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!
Visit the O'Reilly Network http://www.oreillynet.com for more online content. |
Next: MyISAM Tables >>
More MySQL Articles
More By O'Reilly Media
/ 46 
