MariaDB Enterprise Server InnoDB Indexes

Columns in InnoDB tables can be indexed. The optimizer can use indexes for certain types of queries to improve the performance.

Types of Indexes

There are several types of indexes:

Type

Description

Primary Keys

  • 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 MIXED or ROW!

  • For best performance, users should always create primary keys for their tables.

Secondary Indexes

  • 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

  • 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

  • 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

  • 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.

Indexing Columns

Columns can be indexed in different ways:

Type

Description

Column Indexes

  • An index on a single column.

  • All index types support column indexes.

Composite (Multi-Column) Indexes

  • An index on multiple columns.

  • All index types except spatial indexes support composite indexes.

Indexing Column Values

Column values can be indexed in different ways:

Column Part

Description

Full Value

  • The full value of the column is indexed.

  • For example, if a VARCHAR column can store 500 characters, then all 500 characters can be indexed for each row.

  • Full values cannot be indexed for some data types.

  • If a full column value cannot be indexed, then InnoDB will automatically index the maximum prefix length for the column instead.

Prefix

  • A specific prefix length of the column is indexed.

  • For example, if a VARCHAR column can store 500 characters, but the column's prefix length for the index is set to 100, then only the first 100 characters can be indexed for each row.