Exploring the Various Table Types

There are two transaction-safe table types (InnoDB and BDB), but the rest (ISAM, MyISAM, MERGE, and HEAP) are not transaction safe. Choosing the right table type can dramatically affect performance speed.

ISAM Tables

The Indexed Sequential Access Method (ISAM) table type was the old MySQL standard. The MyISAM table type replaced it in version 3.23.0 (although ISAM types will still be available until MySQL 4.1). So, you'll probably only encounter the ISAM table type if you're dealing with old databases dating from that time. The main difference between the two is that the index on a MyISAM table is much smaller than on an ISAM table, so a SELECT using an index on a MyISAM table will use fewer system resources. The flipside is that MyISAM uses more processor power to insert a record into the more compressed index.

ISAM tables have the following features:

ISAM stores the data files with an .ISD and the index file with an .ISM extension.
It is not binary portable across different machines or operating systems. In other words, you cannot just copy the ISD and ISM files. You'll have to use one of the other backup methods, such as mysqldump (see Chapter 11, "Database Backups").

If you do run into the ISAM table type, you should change it to the more efficient MyISAM type. MyISAM tables also allow you to use more of MySQL's built-in functionality. To convert an ISAM table to a MyISAM table, use the following:

ALTER TABLE tablename TYPE = MYISAM;

MyISAM Tables

The MyISAM table type replaced ISAM in version 3.23.0. MyISAM indexes are much smaller than ISAM indexes, so the system will use fewer resources when doing a SELECT using an index on a MyISAM table. However, MyISAM uses more processor power to insert a record into the more compressed index.

MyISAM data files are given the extension .MYD, and the indexes have the extension .MYI. MyISAM databases are stored in a directory, so if you've been doing the examples from Chapter 1 and have permission to look inside the directory firstdb, you'll see the following files:

sales_rep.MYI
sales_rep.MYD
sales.MYD
sales.MYI
customer.MYD
customer.MYI

The data files should always be larger than the index files. In Chapter 4 you'll see how to use indexes properly and what they actually contain.

There are three subtypes of MyISAM tables: static, dynamic, or compressed.

MySQL decides whether to use dynamic or static tables when the table is created. Static tables are the default format, which exist if there are no VARCHAR, BLOB, or TEXT columns. If any of these column types exist, the table type becomes dynamic.

Static Tables

Static tables (also more descriptively called fixed-length tables) are of a fixed length. Look at Figure 2.1, which shows the characters stored in one field of a mini-table. The field is a first name, set to CHAR(10).

click to expand
Figure 2.1: Date stores in static format

There are exactly 10 bytes stored for each record. If the actual name takes up fewer, the rest of the column is padded with spaces to fit the full 10 characters.

Characteristics of static tables include the following:

Very quick (because MySQL knows the second name always starts at the 11th character)
Easy to cache
Easy to reconstruct after a crash (again, because the positions of records are fixed, MySQL knows where each record is, so only a record being written during the crash will be lost)
Requires more disk space (30 characters needed for the 3 records, even though only 16 are used for the names)
Not necessary to reorganize with myisamchk (see Chapter 10, "Basic Administration," for more on this)

Dynamic Tables

Columns in dynamic tables are of different lengths. If the same data used in the static table is placed into a dynamic table, it will be stored as shown in Figure 2.2.

Figure 2.2: Data stored in dynamic format

Although this data format saves space, it is more complex. Each record has a header, which indicates how long it is.

Characteristics of dynamic table types include the following:

All string columns are dynamic (unless they are less than 4 bytes. In this case, the space saved would be negligible, and the extra complexity would lead to a performance loss).
Usually takes much less disk space than fixed tables.
Tables require regular maintenance to avoid fragmentation. (For example, if you updated Ian to Iane, the e could not appear in the space immediately after the n because this space is occupied by the start of the next column or record.) See Chapter 10 for further details on maintenance.
In the case of fragmented columns, each new link incurs a penalty of 6 bytes and will be at least 20 bytes in size (and may have links of its own as well if further updates increase the size beyond this).
Not as easy to reconstruct after a crash, especially if heavily fragmented.
Excluding links, the size of a dynamic record can be calculated with this formula:

3

+ (number of columns + 7) / 8

+ (number of char columns)

+ packed size of numeric columns

+ length of strings

+ (number of NULL columns + 7) / 8
Each record has a header, which indicates which string columns are empty and which numeric columns contain a zero (not NULL records), in which case they are not stored to disk. Nonempty strings contain a length byte, plus the string contents.

Compressed Tables

Compressed tables are read-only table types that use much less disk space. They are ideal for use with archival data which will not change (as they can only currently be read from, not written to), and where not much space is available, such as for a CD-ROM.

Characteristics of compressed tables include the following:

Created using the myisampack utility (note that the ROW_FORMAT="compressed" option for CREATE TABLE will only work if the myisampack code has been added to the server).
Tables are much smaller.
Because each record is separately compressed, there is little access overhead.
Each column could be compressed differently, using different compression algorithms.
Can compress fixed or dynamic table formats.
To create a compressed table with myisampack, simply run the following:
```
myisampack [options] filename
```

Table 2.6 shows the options for compressed tables.

Table 2.6: Compressed Table Options
Option	Description
-b, --backup	Creates a backup of the table, calling it tablename.OLD.
-#, --debug=debug_options	Outputs debug log. The debug_options string often is d:t:o,filename.
-f, --force	When compressing, MySQL creates a temporary file called tablename.TMD. If the compression process dies for some reason, this file may not have beendeleted. This option forces MySQL to pack the table even if the temporary file exists, if the compression causes the table to become bigger, or if the table is too small to compress in the first place.
-?, --help	Displays a help message and exits.
-j big_tablename, --join=big_tablename	Joins all tables listed on the command line into one big table. All tables that you want to combine must be identical (in all aspects such as columns and indexes).
-p #, --packlength=#	Usually you'd only use this option when you're running myisampack a second time. myisampack stores all rows with a length pointer from 13. Occasionally, myisampack notices that it should have used a shorter-length pointer during the process (normally it gets it right!). Next time youpack the table, you can alert myisampack to use the optimal length storage size.
-s, --silent	Silent mode. Only outputs errors.
-t, --test	This option will not actually pack the table; it will just test the packing process.
-T dir_name, --tmp_dir= dir_name	Writes the temporary table into the directory you specify.
-v, --verbose	Verbose mode. Writes information about progress and result of the packing.
-V, --version	Displays version information and exits.
-w, --wait	If the table is in use, this option waits and retries. Using this option in conjunction with --skip-external-locking is not recommended ifthere is a possibility of the table being updated while you're packing.

Let's compress one of the tables you've used so far. You have to use the -f option because the table is too small to compress normally:

C:\Program Files\MySQL\bin>myisampack -v -f   ..\data\firstdb\sales_~1 Compressing ..\data\firstdb\sales_~1.MYD: (5 records) - Calculating statistics normal:      3  empty-space:       0  empty-zero:         2  empty-fill:   1 pre-space:   0  end-space:         2  intervall-fields:   0  zero:         0 Original trees:  7  After join: 1 - Compressing file Min record length:     10   Max length:     17   Mean total length:     40 -35.81%

To unpack a table, run myisamchk --unpack filename:

C:\Program Files\MySQL\bin>myisamchk --unpack ..\data\firstdb\sales_~1 - recovering (with keycache) MyISAM-table '..\data\firstdb\sales_~1' Data records: 5

MERGE Tables

MERGE tables are amalgamations of identical MyISAM tables. They were introduced in version 3.23.25. You'd normally use them only when your MyISAM tables are getting too big.

The advantages of MERGE tables include the following:

More speed in some situations (you could split different tables onto different disks, and use a MERGE table to access them as one table).
Smaller table size. Some operating systems have a file size limit, and splitting the tables and creating a MERGE table allows one to get around this. Also, files can more easily transferred, such as by copying them to CD.
You can make most of the original tables read-only and allow INSERTs into the most recent table. This means you run the risk of only one small table getting corrupted during an UPDATE or INSERT, and the repairs on this table would be much quicker.

The disadvantages of MERGE tables include the following:

They are much slower on eq_ref searches.
You need to take care when changing one of the underlying tables, as this will corrupt the MERGE table (no actual harm is done, just the MERGE table may be unavailable).
REPLACE doesn't work.
Tables use slightly more file descriptors.

Let's create a MERGE table. First, you'll need to create two identical tables:

 CREATE TABLE sales_rep1 ( id INT AUTO_INCREMENT PRIMARY KEY, employee_number INT(11), surname VARCHAR(40), first_name VARCHAR(30), commission TINYINT(4), date_joined DATE, birthday DATE ) TYPE=MyISAM; CREATE TABLE sales_rep2 ( id INT AUTO_INCREMENT PRIMARY KEY, employee_number INT(11), surname VARCHAR(40), first_name VARCHAR(30), commission TINYINT(4), date_joined DATE, birthday DATE ) TYPE=MyISAM; CREATE TABLE sales_rep1_2 ( id INT AUTO_INCREMENT PRIMARY KEY, employee_number INT(11), surname VARCHAR(40), first_name VARCHAR(30), commission TINYINT(4), date_joined DATE, birthday DATE ) TYPE=MERGE UNION=(sales_rep1,sales_rep2);

Let's insert some data into the tables so you can test it later:

 INSERT INTO sales_rep1 ('employee_number', 'surname',  'first_name', 'commission', 'date_joined', 'birthday')  VALUES (1,'Tshwete','Paul',15,'1999-01-03','1970-03-04'); INSERT INTO sales_rep2 ('employee_number', 'surname',  'first_name', 'commission', 'date_joined', 'birthday')  VALUES (2,'Grobler','Peggy-Sue',12,'2001-11-19','1956-08-25');

Now, if you do a query on the merged table, all the records in sales_rep1 and sales_ rep2 are available:

mysql> SELECT first_name,surname FROM sales_rep1_2; +------------+---------+ | first_name | surname | +------------+---------+ | Paul       | Tshwete | | Peggy-Sue  | Grobler | +------------+---------+ 2 rows in set (0.00 sec)

Based on the previous results, you don't know which underlying table any of the records are in. Fortunately, if you're updating a record, you don't need to know this. The following statement

mysql> UPDATE sales_rep1_2 set first_name = "Peggy"  WHERE first_name="Peggy-Sue"; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0

will update the record correctly. Because the record only physically exists on the underlying level, queries to both the MERGE table and the underlying MyISAM table will reflect the correct data, as the following demonstrates:

mysql> SELECT first_name,surname FROM sales_rep1_2; +------------+---------+ | first_name | surname | +------------+---------+ | Paul       | Tshwete | | Peggy      | Grobler | +------------+---------+ 2 rows in set (0.00 se mysql> SELECT first_name,surname FROM sales_rep2; +------------+---------+ | first_name | surname | +------------+---------+ | Peggy      | Grobler | +------------+---------+ 1 row in set (0.00 sec)

The same applies to DELETE statements:

mysql> DELETE FROM sales_rep1_2 WHERE first_name='Peggy'; Query OK, 1 row affected (0.00 sec)

The record is removed at the underlying level, so it will disappear from queries to both the MERGE table and the underlying table:

mysql> SELECT first_name,surname FROM sales_rep1_2; +------------+---------+ | first_name | surname | +------------+---------+ | Paul       | Tshwete | +------------+---------+ 1 row in set (0.00 sec) mysql> SELECT first_name,surname FROM sales_rep2; Empty set (0.00 sec)

However, if you tried to perform an INSERT, MySQL would not know which underlying table to insert the record to, and so it will return an error:

mysql> INSERT INTO sales_rep1_2 ('surname', 'first_name',  'commission', 'date_joined', 'birthday')  VALUES ('Shephard','Earl',11,'2002-12-15','1961-05-31'); ERROR 1031: Table handler for 'sales_rep1_2' doesn't have this option

Luckily there is a solution, introduced in version 4 (before this, you could not insert into the MERGE table at all). When creating your MERGE table, you can specify which table to insert to. Look at the last clause of the following CREATE statement:

 CREATE TABLE sales_rep1_2 ( id INT AUTO_INCREMENT PRIMARY KEY, employee_number INT(11), surname VARCHAR(40), first_name VARCHAR(30), commission TINYINT(4), date_joined DATE, birthday DATE ) TYPE=MERGE UNION=(sales_rep1,sales_rep2) INSERT_METHOD = LAST

The INSERT_METHOD can be NO, FIRST, or LAST. Inserted records are then placed into the first table in the union list, the last table, or none at all. The default is NO.

Warning

If you perform any structural changes to the underlying tables, such as renaming them or rebuilding the indexes, you'll need to rebuild the MERGE table. Drop the MERGE table, then make your changes, and then rebuild the MERGE table. If you make the changes and forget to drop your MERGE table, you may find you are unable to access the MERGE table properly. Dropping and rebuilding will solve this.

HEAP Tables

HEAP tables are the fastest table types because they are stored in memory and use a hashed index. The downside is that, because they are stored in memory, all data will be lost in the case of a crash. They also can't hold quite so much data (unless you've got a big budget for RAM).

As with any table, you can create a table based on the contents of another table. HEAP tables are often used to give faster access to an already existing table—to leave the original table for inserting and updating and then have the new table for fast reading. Let's create one from the sales_rep table. If you haven't already created the sales_rep table, create and populate the sales_rep table with the following statements:

 CREATE TABLE sales_rep (   employee_number int(11) default NULL,   surname varchar(40) default NULL,   first_name varchar(30) default NULL,   commission tinyint(4) default NULL,   date_joined date default NULL,   birthday date default NULL ) TYPE=MyISAM; INSERT INTO sales_rep VALUES (1, 'Rive', 'Sol', 10,  '2000-02-15', '1976-03-18'); INSERT INTO sales_rep VALUES (2, 'Gordimer', 'Charlene', 15,  '1998-07-09', '1958-11-30'); INSERT INTO sales_rep VALUES (3, 'Serote', 'Mike', 10,  '2001-05-14', '1971-06-18'); INSERT INTO sales_rep VALUES (4, 'Rive', 'Mongane', 10,  '2002-11-23', '1982-01-04');

Now, you create a HEAP table that will take a subset of sales_rep and put it into memory for fast access:

mysql> CREATE TABLE heaptest TYPE=HEAP SELECT first_name,surname  FROM sales_rep; Query OK, 4 rows affected (0.02 sec) Records: 4  Duplicates: 0  Warnings: 0 mysql> SELECT * FROM heaptest; +------------+----------+ | first_name | surname  | +------------+----------+ | Sol        | Rive     | | Charlene   | Gordimer | | Mike       | Serote   | | Mongane    | Rive     | +------------+----------+ 4 rows in set (0.00 sec)

Characteristics of HEAP tables include the following:

Because HEAP tables use memory, you don't want them hogging too much. The tables are always limited by the mysqld variable max_heap_table_size.
Keys are not used in the same way as for MyISAM tables. They cannot be used for an ORDER BY.
They only use the whole key to search for a row, not parts of a key.
They only use = and <=> when searching indexes.
MySQL's range optimizer cannot find out how many rows there are between two values.
However, when keys are used properly in HEAP tables, they are fast!
HEAP tables, unlike many other hashed tables, allow nonunique keys.
They do not support an index on a NULL column.
They do not support AUTO_INCREMENT columns.
Do not support BLOB or TEXT columns.

As you can see, there are quite a few differences between MyISAM indexes and HEAP indexes. A HEAP table could actually be slower if you're relying on an index it does not use. See Chapter 4 for more on using keys.

Note

Besides the max_heap_table_size limit and the memory limit on your machine, a limit of 4GB per table could be reached on some setups because this is the limit imposed by a 32-bit machine's address space.

InnoDB Tables

InnoDB tables are a transaction-safe table type (this means you have COMMIT and ROLLBACK capabilities. In a MyISAM table, the entire table is locked when inserting. Just for that fraction of a second, no other statements can be run on the table. InnoDB uses row-level locking so that only the row is locked, not the entire table, and statements can still be performed on other rows.

For performance purposes you should use InnoDB tables if your data performs large numbers of INSERTs or UPDATEs relative to SELECTs. MyISAM would be a better choice when your database performs large numbers of SELECTs relative to UPDATEs or INSERTs.

To use InnoDB tables, MySQL will need to have been compiled with InnoDB support (see Chapter 15, "Installing MySQL," for full details), such as the mysqld-max distribution. There are also a number of configuration parameters that you should set up before you can rely on this table type for good performance, so be sure to read Chapter 13, "Configuring and Optimizing MySQL," for more details.

When you start MySQL with InnoDB options compiled, and use only the defaults, you'll see something like the following:

C:\MySQL\bin>mysqld-max InnoDB: The first specified data file .\ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file .\ibdata1 size to 64 MB InnoDB: Database physically writes the file full: wait... InnoDB: Log file .\ib_logfile0 did not exist: new to be created InnoDB: Setting log file .\ib_logfile0 size to 5 MB InnoDB: Log file .\ib_logfile1 did not exist: new to be created InnoDB: Setting log file .\ib_logfile1 size to 5 MB InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 020504 12:42:52  InnoDB: Started C:\MYSQL\BIN\MYSQLD~2.EXE: ready for connections

Warning

Before version 4, you could not just start MySQL. You had to set at least the innodb_data_file_path in the configuration file. This config file is discussed more fully in Chapter 13.

By default, MySQL creates a file ibdata1 in the default data directory (usually C:\MSQL\data on Windows, or /usr/local/mysql/data or /usr/local/var/ on Unix).

InnoDB tables are different from MyISAM tables in that the databases are not stored in a directory, with the tables as files. All tables and indexes are stored in an InnoDB tablespace (which can consist of one or more tables; in the previous example it's ibdata1).

The table data limit then is not limited to the operating system file size limit.

Note

In a MyISAM table, an operating system with a 2GB limit will allow a maximum table size of 2GB. InnoDB has no such limit, although the onus is then on the administrator to optimize the tablespace.

The initial size of this table is set to 16MB. In early versions of MySQL 4, this was set to 64MB but was nonextending (meaning that once you ran out of space, that was it!). Later versions set this to auto-extending by default, meaning that as your data grows, so does the tablespace. You'll want to have some hands-on control over this to optimize performance. This is explained briefly in a bit and in more detail in Chapter 13.

To create an InnoDB table, use the following:

mysql> CREATE TABLE innotest (f1 INT,f2 CHAR(10),INDEX (f1)) TYPE=InnoDB; Query OK, 0 rows affected (0.10 sec)

BDB Tables

BDB stands for Berkeley Database (it was originally created at the University of California, Berkeley). It is also a transaction-capable table type. As with InnoDB tables, BDB support needs to be compiled into MySQL to make it work (the mysql-max distribution comes with BDB support).

To create a BDB table, simply use TYPE=BDB after your CREATE TABLE statement:

mysql> CREATE TABLE bdbtest(f1 INT,f2 CHAR(10)) TYPE=BDB; Query OK, 0 rows affected (0.28 sec)

Currently, the interface between MySQL and BDB (which exists independently to MySQL) is still in beta. MySQL and BDB have both been around for ages, and are stable, but the interface between the two has not been. Check the latest documentation to see whether this is still the case.