Oracle 5.0, Reference Manual

Page: 1042 / 3232

CREATE INDEX
Syntax
1022
The statement shown here creates an index using the first 10 characters of the
name
column:
CREATE INDEX part_of_name ON customer (name(10));
If names in the column usually differ in the first 10 characters, this index should not be much slower
than an index created from the entire
name
column. Also, using column prefixes for indexes can make
the index file much smaller, which could save a lot of disk space and might also speed up
INSERT
operations.
Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example,
a prefix can be up to 1000 bytes long for
MyISAM
tables, and 767 bytes for
InnoDB
tables. The
NDBCLUSTER
storage engine does not support prefixes (see Section 17.1.5.6, “Unsupported or Missing
Features in MySQL Cluster” ).
Note
Prefix limits are measured in bytes, whereas the prefix length in
CREATE
INDEX
statements is interpreted as number of characters for nonbinary data
types (
CHAR
,
VARCHAR
,
TEXT
). Take this into account when specifying a prefix
length for a column that uses a multi-byte character set.
A
UNIQUE
index creates a constraint such that all values in the index must be distinct. An error occurs
if you try to add a new row with a key value that matches an existing row. This constraint does not
apply to
NULL
values except for the
BDB
storage engine. For other engines, a
UNIQUE
index permits
multiple
NULL
values for columns that can contain
NULL
. If you specify a prefix value for a column in a
UNIQUE
index, the column values must be unique within the prefix.
FULLTEXT
indexes are supported only for
MyISAM
tables and can include only
CHAR
,
VARCHAR
,
and
TEXT
columns. Indexing always happens over the entire column; column prefix indexing is not
supported and any prefix length is ignored if specified. See Section 12.9, “Full-Text Search Functions” ,
for details of operation.
The
MyISAM
,
InnoDB
,
NDB
,
BDB
, and
ARCHIVE
storage engines support spatial columns such as
(
POINT
and
GEOMETRY
. ( Section 12.16, “Spatial Extensions” , describes the spatial data types.)
However, support for spatial column indexing varies among engines. Spatial and nonspatial indexes
are available according to the following rules.
Spatial indexes (created using
SPATIAL INDEX
) have these characteristics:
• Available only for
MyISAM
tables. Specifying
SPATIAL INDEX
for other storage engines results in
an error.
• Indexed columns must be
NOT NULL
.
• In MySQL 5.0, the full width of each column is indexed by default, but column prefix lengths are
permitted. However, as of MySQL 5.0.40, the length is not displayed in
SHOW CREATE TABLE
output.
mysqldump
uses that statement. As of that version, if a table with
SPATIAL
indexes
containing prefixed columns is dumped and reloaded, the index is created with no prefixes. (The full
column width of each column is indexed.)
Characteristics of nonspatial indexes (created with
INDEX
,
UNIQUE
, or
PRIMARY KEY
):
• Permitted for any storage engine that supports spatial columns except
ARCHIVE
.
• Columns can be
NULL
unless the index is a primary key.
• For each spatial column in a non-
SPATIAL
index except
POINT
columns, a column prefix length
must be specified. (This is the same requirement as for indexed
BLOB
columns.) The prefix length is
given in bytes.
• The index type for a non-
SPATIAL
index depends on the storage engine. Currently, B-tree is used.
In MySQL 5.0: