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 14, 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.
For additional information about column indexes, see Section 13.1.13, “CREATE INDEX Syntax”.
syntax in an index specification for a string column, you can
create an index that uses only the first
N characters of the column. Indexing
only a prefix of column values in this way can make the index
file much smaller. When you index 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
Prefix limits are measured in bytes, whereas the prefix length
ALTER TABLE, and
CREATE INDEX statements is
interpreted as number of characters for nonbinary string types
TEXT) and number of bytes for
binary string types (
BLOB). Take this into account
when specifying a prefix length for a nonbinary string column
that uses a multibyte character set.
For additional information about index prefixes, see Section 13.1.13, “CREATE INDEX Syntax”.
FULLTEXT indexes 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 12.9, “Full-Text Search Functions”.
You can create indexes on spatial data types. Only
MyISAM supports R-tree indexes on spatial
types. Other storage engines use B-trees for indexing spatial
types (except for
NDB, which do not support spatial
MEMORY storage engine uses
HASH indexes by default, but also supports