Understanding Table Types and Indexes

Each table type has its own behavior when it comes to indexes, and each will handle them differently. Not all index types are available to each table type. It's important to understand how you're going to be using a table, and therefore what indexes you may require, before you commit to a table type. Sometimes what looks like an ideal table type turns out to be no good because a certain type of index is not available. The following lists highlight features and differences of indexes for each table type.

MyISAM tables have the following attributes:

• Indexes are stored in files with the extension .MYI.

• Number indexes are stored with the high byte first to allow better compression of the index.

• BLOB and TEXT indexes can exist.

• Null values are permissible in indexes (not primary keys).

• The data and the index can be in different directories (which allows greater speed).

MERGE tables have the following attributes:

• A MERGE table contains no indexes of its own.

• The .MRG file contains a list of the .MYI index files from the component MyISAM tables.

• You still need to specify indexes when you create a MERGE table.

HEAP tables have the following attributes:

• Use a hash index stored in memory, which is very fast.

• Can only use indexes with the = and <=> operators.

• Cannot use an index on a column that allows NULL values.

• Indexes are not used with an ORDER BY clause.

• MySQL cannot find out approximately how many rows there are between two values (this result is used by the query optimizer to decide which index is most efficient to use). See the "Helping MySQL's Query Optimizer with ANALYZE" section later in this chapter for more on this.

ISAM tables use a B-Tree index stored in files with an extension of .ism.

InnoDB tables cannot use full-text indexes.