# 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](https://docs.singlestore.com/db/v9.1/introduction/distributed-architecture/sharding.md)and[Choosing a Shard Key](https://docs.singlestore.com/db/v9.1/introduction/distributed-architecture/sharding/#section-idm4501210417348832654841869645.md)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](https://docs.singlestore.com/db/v9.1/create-a-database/optimizing-table-data-structures.md)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](https://docs.singlestore.com/db/v9.1/create-a-database/optimizing-table-data-structures.md)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()`.SingleStorerecommends 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](https://docs.singlestore.com/db/v9.1/create-a-database/rowstore.md)and[Choosing Rowstore Keys](https://docs.singlestore.com/db/v9.1/create-a-database/optimizing-table-data-structures.md)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 inSingleStoreare 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](https://docs.singlestore.com/db/v9.1/create-a-database/other-schema-concepts.md)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](https://docs.singlestore.com/db/v9.1/create-a-database/other-schema-concepts.md)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. See[Create Table with FULLTEXT Columns](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table/#UUID-5a7c66e9-736e-ce79-7a7e-1106691d9e5a.md)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` | | **Vector Index** | The vector index provides Approximate Nearest Neighbor (ANN) search which finds a set of k nearest neighbors very efficiently. | Vector indexes may only be defined on columnstore tables.Refer to[Vector Indexing](https://docs.singlestore.com/db/v9.1/reference/sql-reference/vector-functions/vector-indexing.md)for the syntax to define this type of index | N/A | A vector index must be built on a single column of type`VECTOR (, [F32])`where``is the number of dimensions.The element type is restricted to`F32`. | | **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](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-geospatial-features.md)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` | | **Multi-Value Hash Index** | Maps values to the row IDs containing the value. | Multi-value hash index is useful in searching for elements within composite data structures like arrays, BSON documents, etc.Refer to[Multi-Value Hash Index (BSON)](https://docs.singlestore.com/db/v9.1/reference/sql-reference/bson-functions/multi-value-hash-index-bson.md)or[Multi-Value Hash Index (JSON)](https://docs.singlestore.com/db/v9.1/create-a-database/multi-value-hash-index-json.md)for creating multi-key indexes on the respective column types. | N/A | Multi-value hash index is supported only on JSON and BSON type columns. | *** Modified at: October 1, 2025 Source: [/db/v9.1/create-a-database/understanding-keys-and-indexes-in-singlestore/](https://docs.singlestore.com/db/v9.1/create-a-database/understanding-keys-and-indexes-in-singlestore/) (An index of the documentation is available at /llms.txt)