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 columnstore or rowstore tables. See Sharding 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. To prevent an index on from being created on a shard key, use the `METADATA_ONLY` option when creating the shard key. This will decrease memory usage, but it can cause queries to run slower.	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 columnstore or rowstore 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 `SORT KEY()` syntax in a `CREATE TABLE` statement. When a key is specified, a sort key index is created, which defines the order in which rows are sorted in the columnstore table. The sort key can be specified as ascending or descending. When a key is not specified, no index is created and the columnstore data is stored unsorted. If you want to create an unsorted columnstore table, you can specify an empty key using `SORT KEY()`. `KEY() USING CLUSTERED COLUMNSTORE` is a legacy syntax that is equivalent to `SORT KEY()`. SingleStore recommends using `SORT KEY()`.	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. Skiplists in SingleStore are meant to replace the B-Tree indexes used by most other databases, including MySQL.	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 columnstore or rowstore 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 or multiple columns are allowed per hash index.	Cannot be created on columns with the following dataypes: `FLOAT`, `REAL`, or `DOUBLE`
Full-text index	Full-text search allows searching for words or phrases in a large body of text through an inverted index.	Full-text 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 data types: `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 data types: `GEOGRAPHY`, and `GEOGRAPHYPOINT`
Generalized Inverted Index(GIN)	GIN is a multi-valued index that maps values to the row IDs containing the value.	GIN is useful in searching for elements within composite data structures like arrays, BSON documents, etc. See Generalized Inverted Index (GIN) (BSON) for the syntax to define this type of index.	N/A	GIN is supported only on BSON type column.

Understanding Keys and Indexes in SingleStore

Was this article helpful?

Was this article helpful?