All MySQL data types can be indexed. Use of indexes on the
relevant columns is the best way to improve the performance of
The maximum number of indexes per table and the maximum index length is defined per storage engine. See Chapter 13, Storage Engines. All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes. Most storage engines have higher limits.
MyISAM and (as of MySQL 4.0.14)
InnoDB storage engines also support indexing
TEXT columns. When indexing a
TEXT column, you
must specify a prefix length for the index.
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
Prefixes can be up to 1000 bytes long (767 bytes for
InnoDB tables). (Before MySQL 4.1.2, the
limit is 255 bytes for all tables.) Note that prefix limits are
measured in bytes, whereas the prefix length in
CREATE TABLE statements is
interpreted as number of characters. Be sure to take
this into account when specifying a prefix length for a column
that uses a multi-byte character set.
As of MySQL 3.23.23, you can also create
FULLTEXT indexes. They are used for full-text
searches. Only the
MyISAM storage engine
FULLTEXT indexes and only for
TEXT columns. Indexing always
takes place over the entire column and column prefix indexing is
not supported. For details, see
Section 11.9, “Full-Text Search Functions”.
As of MySQL 4.1.0, you can create indexes on spatial data types.
Spatial indexes use R-trees. Currently, only
MyISAM supports indexes on spatial types.
HASH indexes by default. It also
BTREE indexes as of MySQL 4.1.0.