The most common type of index involves a single column, storing copies of the values from that column in a data
structure, allowing fast lookups for the rows with the corresponding column values. The B-tree data structure
lets the index quickly find a specific value, a set of values, or a range of values, corresponding to operators
IN, and so on, in a
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.
syntax in an index specification, you can create an index
that uses only the first
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
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
MyISAM storage engines support
and only for
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".
Optimizations are applied to certain kinds of
FULLTEXT queries against single
InnoDB tables. Queries with these characteristics are particularly efficient:
FULLTEXT queries that only return the document ID, or
the document ID and the search rank.
FULLTEXT queries that sort the matching rows in
descending order of score and apply a
LIMIT clause to take the top N
matching rows. For this optimization to apply, there must be no
clauses and only a single
ORDER BY clause in descending order.
FULLTEXT queries that retrieve only the
COUNT(*) value of rows matching a search term, with no additional
WHERE clauses. Code the
WHERE clause as
WHERE MATCH(, without any
text) AGAINST ('
0 comparison operator.
You can also create indexes on spatial data types. Currently, only
R-tree indexes on spatial types. Other storage engines use B-trees for indexing spatial types (except for
ARCHIVE, which does not support spatial type indexing).
MEMORY storage engine uses
HASH indexes by
default, but also supports