Choosing Rowstore Keys
On this page
You may define indexes, also called keys, on SingleStoreDB rowstore tables.
There are two storage types for rowstore indexes: a lockfree skiplist and a lockfree hash table.
By default, indexes are stored as skiplists, which have similar functional and performance characteristics as B-trees in other databases.
A skiplist is a data structure optimized for ordered data that stores rows in collections of increasingly smaller ordered lists. Queries can quickly seek data by binary searching using the different sized lists and can quickly scan over ranges of data by iterating over the largest list. For multi-column indexes, query filters must match a prefix of the index column list to be able to take advantage of the index.
A hash table is a data structure optimized for fast lookups, which stores rows in a sparse array of buckets indexed by a hash function on the relevant columns.
Queries can quickly find exact match data by examining only the bucket identified by the hash function, but cannot easily scan over a subset of the table. For multi-column indexes, query filters must match all of the index columns to be able to take advantage of the index. Due to this inflexibility, we discourage the use of hash indexes. They should only be used when there is a demonstrated need and measurable benefit on your particular dataset and workload.
Another consideration when choosing an index is the overhead of adding another index.
Each rowstore table may have at most one primary key and optionally many secondary keys.
For more information, refer to the following two resources:
Q: For the table,
CREATE ROWSTORE TABLE t(a INT, b INT, KEY (a, b)), will the query
SELECT SUM(a) FROM t WHERE b = 3 benefit from the index?
A: No, since the only column in the filter list,
b, is not a prefix of the key
(a, b), the query cannot benefit from the index.
SELECT SUM(a) FROM t WHERE a = 3 would be able to benefit from the index since
a is a prefix of the key
Last modified: February 22, 2023