8.3.5 Column Indexes

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 =,
>, ≤,
BETWEEN, IN, and so on, in
a WHERE clause.

Index Prefixes

With
col_name(N)
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
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 767 bytes long for
InnoDB tables that use the
REDUNDANT
or
COMPACT
row format. The prefix length limit is 3072 bytes for
InnoDB tables that use the
DYNAMIC
or
COMPRESSED
row format. For MyISAM tables, the prefix length limit is 1000
bytes.

Note

Prefix limits are measured in bytes, whereas the prefix
length in CREATE TABLE,
ALTER TABLE, and
CREATE INDEX statements is
interpreted as number of characters for nonbinary string
types (CHAR,
VARCHAR,
TEXT) and number of bytes for
binary string types (BINARY,
VARBINARY,
BLOB). Take this into account
when specifying a prefix length for a nonbinary string
column that uses a multibyte character set.

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
WHERE 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(text)
AGAINST
('other_text'),
without any > 0 comparison 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
may show Select tables optimized away in
the Extra column due to matching occurring
during optimization; in this case, no table access need occur
during later execution.

Spatial Indexes

You can create indexes on spatial data types.
MyISAM and InnoDB
support 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).