All MySQL data types can be indexed. Use of indexes on the
relevant columns is the best way to improve the performance of
SELECT operations.
The maximum number of indexes per table and the maximum index
length is defined per storage engine. See
Chapter 14, Storage Engines and Table Types. 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.
With
col_name(N)N characters of a
string column. Indexing only a prefix of column values in this
way can make the index file much smaller. When you index a
BLOB or TEXT column, you
must specify a prefix length for the index.
For example:
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
Prefixes can be up to 1000 bytes long (767 bytes for
InnoDB 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.
You can also create FULLTEXT indexes. These
are used for full-text searches. Only the
MyISAM storage engine supports
FULLTEXT indexes and only for
CHAR, VARCHAR, and
TEXT columns. Indexing always takes place
over the entire column and partial (column prefix) indexing is
not supported. For details, see
Section 12.7, “Full-Text Search Functions”.
You can also create indexes on spatial data types. Currently,
only MyISAM supports R-tree indexes on
spatial types. Other storage engines use B-trees for indexing
spatial types (except for ARCHIVE and
NDBCLUSTER, which do not support spatial type
indexing).
The MEMORY storage engine uses
HASH indexes by default, but also supports
BTREE indexes.
