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 | 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.
| 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
| 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 SingleStoreDB 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: |
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 | N/A | Can only be created on columns with the following datatypes: |
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: |