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 such as
IN, and so on, in
The maximum number of indexes per table and the maximum index length is defined per storage engine. See Chapter 14, The InnoDB Storage Engine, and Chapter 15, Alternative 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. 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, unless you have
Prefix limits are measured in bytes, whereas the prefix
ALTER TABLE, and
CREATE INDEX statements is
interpreted as number of characters for nonbinary string
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.
If a search term exceeds the index prefix length, the index is used to exclude non-matching rows, and the remaining rows are examined for possible matches.
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 engines support
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”.
Optimizations are applied to certain kinds of
FULLTEXT queries against single
InnoDB tables. Queries with these
characteristics are particularly efficient:
FULLTEXTqueries that only return the document ID, or the document ID and the search rank.
FULLTEXTqueries that sort the matching rows in descending order of score and apply a
LIMITclause to take the top N matching rows. For this optimization to apply, there must be no
WHEREclauses and only a single
ORDER BYclause in descending order.
FULLTEXTqueries that retrieve only the
COUNT(*)value of rows matching a search term, with no additional
WHEREclauses. Code the
WHERE MATCH(, without any
text) AGAINST ('
> 0comparison operator.
For queries that contain full-text expressions, MySQL evaluates those expressions during the optimization phase of query execution. The optimizer does not just look at full-text expressions and make estimates, it actually evaluates them in the process of developing an execution plan.
An implication of this behavior is that
EXPLAIN for full-text queries
is typically slower than for non-full-text queries for which
no expression evaluation occurs during the optimization phase.
EXPLAIN for full-text queries
Select tables optimized away in
Extra column due to matching occurring
during optimization; in this case, no table access need occur
during later execution.
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
ARCHIVE, which does not
support spatial type indexing).