A table's primary key index is used to uniquely identify every row.
There can only be a single primary key for a given table.
InnoDB uses the primary key as a clustered index, which means that InnoDB stores table data in the order determined by the primary key.
Primary key indexes are B+ trees, which are very efficient for searching for exact values, performing range scans, and checking uniqueness.
If no primary key or
NOT NULL unique index is defined for a table, then InnoDB will automatically create a primary key called
GEN_CLUST_INDEX, using an internal 48-bit
DB_ROW_ID column as the key. Replication with such tables can be very slow, especially when binlog_format is
For best performance, users should always create primary keys for their tables.
Secondary indexes are not used to order the data on disk.
Instead, secondary indexes are only used to improve query performance.
Secondary indexes are B+ trees, which are data structures that are very efficient for searching for exact values, performing range scans, and checking uniqueness.
Unique indexes are used to enforce unique constraints for the indexed column(s).
If a column or set of columns is indexed with a unique index, then each row must have a unique value.
Unique indexes are B+ trees, which are data structures that are very efficient for searching for exact values, performing range scans, and checking uniqueness.
If the table does not have a primary key, but it does have one or more unique indexes on columns declared
NOT NULL, then InnoDB will use the first
NOT NULL unique index as the primary key.
If a unique index is not used as the table's primary key, then it is used as a secondary index.
Full-text indexes are used to perform full-text search of columns that use the CHAR, VARCHAR, LONGTEXT, TEXT, MEDIUMTEXT, and TINYTEXT data types.
Performing a full-text search requires the use of the
MATCH() ... AGAINST() clause.
Full-text indexes are inverted indexes, which map each word into a set of
FTS_DOC_ID values that identify rows where the indexed columns contain the word. The partitioned internal tables
FTS_%_INDEX_% are B+ trees, which are data structures that are very efficient for searching for exact values, performing range scans, and checking uniqueness.
By default, stop words will be excluded.
Certain word lengths will be excluded.
Spatial indexes are used to perform exact-value searches and range scans of columns that use the GEOMETRY, POINT, POLYGON, LINESTRING, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION data types.
Spatial indexes are R-trees, which are data structures that are designed specifically for searching spatial data. The key is a 2-dimensional Minimum Bounding Rectangle of the shape.
Columns must be declared
NOT NULL to be indexed with a spatial index.