Working with Tables
 
Indexing Tables
 
Indexes are the primary means of speeding up access to the contents of your tables, particularly for queries that involve joins on multiple tables.
 
Table Type Indexing Characteristics
 
MySQL provides quite a bit of flexibility in the way you can construct indexes:
 
. You can index single columns or construct composite indexes from combinations of columns.
 
. An index can be allowed to contain duplicate values or required to contain only unique values.
 
. You can have more than one index on a table if you want to be able to look up a values quickly from different columns of a table.
 
. For string column types other than ENUM or SET, you may elect to index a prefix of a column, that is, only the leftmost n bytes. (In fact, for BLOB and TEXT columns, you cannot set up an index unless you do specify a prefix length.)
 
Prefixes can be up to 255 bytes. If the column is mostly unique within the first n bytes, you usually won't sacrifice performance, and may well improve it. Indexing a column prefix rather than the entire column can make an index much smaller and faster to access.
 
Not all table types offer all indexing features. The following table summarizes the indexing properties of the various table types.
 
(The table does not include the MERGE type because MERGE tables are created from MyISAM tables and have similar indexing characteristics.)
 

Index Characteristic

ISAM

MyISAM

HEAP

BDB

InnoDB

NULL values allowed

No

Yes

As of 4.0.2

Yes

Yes

Columns per index

16

16

16

16

16

Indexes per table

16

32

32

31

32

Maximum index row size (bytes)

256

500

500

500/1024

500/1024

Index column prefixes allowed

Yes

Yes

Yes

Yes

No

BLOB/TEXT indexes allowed

No

Yes (255 bytes max)

No

Yes (255 bytes max)

No

 
Two numbers are shown for the BDB and InnoDB index row sizes. For these table types, the size is 500 bytes up through 4.0.3 and 1024 bytes thereafter.
 
The table illustrates some of the reasons why MyISAM storage format generally is to be preferred to the ISAM format that it succeeds. MyISAM relaxes several of the indexing constraints that apply to ISAM tables.
 
For example, with MyISAM tables, you can index columns that contain NULL values, you can index BLOB and TEXT columns, and you can have a larger number of indexes per table.
 
One implication of the differences in indexing characteristics for the various table types is that, depending on your version of MySQL, you may simply not be able to index certain columns. For example, you can use only ISAM tables if your MySQL is older than 3.23, which means you can't index a column if you want it to be able to contain NULL values.
 
Conversely, if you require an index to have certain properties, you may not be able to use certain types of tables. If you need to index a BLOB column, for example, you must use a MyISAM or BDB table.
 
If you have an existing table of one type but would like to convert it to another type that has more suitable indexing characteristics, use ALTER TABLE to change the type. Suppose you have MySQL 3.23 or later but have older tables that were originally created as ISAM tables.
 
You can easily convert them to MyISAM storage format using ALTER TABLE, which allows you to take advantage of MyISAM's superior indexing features:
 
ALTER TABLE tbl_name TYPE = MYISAM;