SingleStore Managed Service

Understanding Keys and Indexes in SingleStore

This page seeks to explain behaviors of and interaction between keys and indexes in SingleStore. A key is a logical concept in relational databases, whereas an index is a physical artifact that assists with processing operations faster or enforcing uniqueness.

For basic keys, there is a one to one relationship with indexes. If the syntax KEY(...) (alone), or PRIMARY KEY(...) is used in CREATE TABLE when defining a given key, an index is created. For all other types of keys that can be defined, an index is not automatically created. For more information about the implications of defining each type of key and/or index, see the below comparison table. In general, this documentation follows MySQL terminology when discussing keys and indexes.

Key or Index

Function

Table Compatibility

Notes and Limitations

Column Compatibility

Shard key

Responsible for distribution of data across partitions. Shard key columns should be as unique as possible.

A shard key (unique or non-unique) can be defined for either rowstore or columnstore tables. See Shard Keys and Choosing a Shard Key for more information.

If a shard and/or primary key is not defined for a given table, that table becomes keylessly sharded.

If a primary key is defined, but a shard key is not, the primary key becomes the shard key.

Only one primary key per table can be created.

The shard key must be a subset of the columns in the primary key, if one is defined.

N/A

Primary key

A group of 1 or more columns from a table that uniquely identifies a row in the table, and serves to prevent data skew.

A primary key can be defined for either rowstore or columnstore tables. See Optimizing Table Data Structures for guidance on choosing a primary key.

The primary key must contain all columns in the shard key.

KEY(...) is the same as PRIMARY KEY(...), except that the latter enforces uniqueness.

N/A

Columnstore sort key

Stores data on disk in a columnstore format.

Columnstore sort keys may only be defined on columnstore tables. See Choosing a Columnstore Key for more information.

A columnstore sort key can be created using the KEY(...) WITH CLUSTERED COLUMNSTORE or SORT KEY(...) option in a CREATE TABLEstatement. When a key is specified, a clustered columnstore index is created, which defines the order in which rows are sorted in the columnstore table. When a key is not specified, no index is created and the columnstore data is stored unsorted.

KEY(...) USING CLUSTERED COLUMNSTORE and SORT KEY(...) are equivalent. They behave identically.

N/A

Rowstore key

Creates an index on a column or group of columns to support fast lookups, or uniqueness enforcement.

A rowstore key may only be defined on rowstore tables. See Rowstore keys and Choosing Rowstore Keys for more information.

The index created with this key can be defined as a skiplist or hash index.

N/A

Skiplist index

A skiplist is a data structure optimized for ordered data that allows for queries to quickly seek data by binary searching.

Skiplist indexes may only be defined on rowstore tables. See Skip List Indexes for more information.

This is the default index type for rowstore tables, unless otherwise specified.

N/A

Hash index

A hash table is a data structure optimized for fast equality lookups by a key.

A hash index can be defined for either rowstore or columnstore tables. See Hash Table Indexes for more information and the syntax to define this type of index.

Only one unique hash index per table can be created, and one column is allowed per hash index.

On tables with a hash index, the shard key can only have one column, which is the same column as the hash index.

Cannot be created on columns with the following dataypes: FLOAT, REAL, or DOUBLE

Fulltext index

Full text search allows searching for words or phrases in a large body of text through an inverted index.

Fulltext indexes may only be defined on columnstore tables, during the CREATE TABLE statement and not after. See FULLTEXT Behavior for the syntax to define this type of index.

N/A

Can only be created on columns with the following datatypes: CHAR, VARCHAR, TEXT, and LONGTEXT

Geospatial index

A geospatial index can be defined on columns containing spatial data and serves to speed up queries on them.

Geospatial indexes may only be defined on rowstore tables. See Working with Geospatial Features for the syntax to define this type of index.

N/A

Can only be created on columns with the following datatypes: GEOGRAPHY, and GEOGRAPHYPOINT