You are viewing an older version of this section. View current production version.
Types of Indexes
SingleStore DB, like all standard SQL databases, allows the creation of indexes on tables, which speed up certain access patterns. As with MySQL, SingleStore DB supports two index type keywords. The BTREE keyword is for compatibility with MySQL syntax and will create the default SingleStore DB skip list index instead. The CLUSTERED COLUMNSTORE index type is for columnstore tables.
Skip List Indexes
The default index type in SingleStore DB is a skip list. Skip lists in SingleStore DB are meant to replace the B-Tree indexes used by most other databases, including MySQL. Skip lists are optimized to run in memory as they can be implemented lock free and offer extremely fast insert performance. Like B-Trees, they offer an expected O(log(n)) lookup performance and can be traversed in sorted order.
Unlike B-Trees in MySQL, skip lists in SingleStore DB are uni-directional (singly linked). Each column in a compound skip list index can be specified as ascending (ASC) or descending (DESC). The default is ASC. Which one you pick will not impact lookup performance, but it does impact scan performance depending on the direction the index is scanned. Scanning a skip list in reverse order is approximately twice as costly as scanning in forward order. So, if you have an ASC index and you run a query that would traverse the index in descending order (ORDER BY DESC for example), then the query will require a more expensive iteration than if the index were DESC.
SingleStore DB supports skip lists only on rowstore tables. For more information on what a Skip List is and why it is used in SingleStore DB see: The Story Behind SingleStore’s Skiplist Indexes
Columnstore Indexes
Columnstore indexes leverage columnstore technology to efficiently store and retrieve large numbers of values from disk (using flash or SSD is recommended). Because columnstore indexes typically provide significant data compression, are backed by disk, and thus don’t have the requirement that all data must fit in memory, which other types of indexes in SingleStore DB do, they are typically very useful for analytical workloads. SingleStore DB currently supports clustered columnstore indexes which, when added to a table, will make the entire table structure backed by the columnstore. Currently columnstore indexes cannot be combined with in-memory row store indexes on the same table. For more information about using columnstore indexes in SingleStore DB, see Columnstore.
Hash Table Indexes
A HASH index will only be utilized if the query employs an equality filter for every column in the index.
Due to the restrictive case detailed above, HASH indexes should only be used when there is a demonstrated need and measurable benefit on your particular dataset and workload. In these specific cases, HASH indexes provide fast exact-match access to unique values. This is because the hash index is stored in a sparse array of buckets indexed by a hash function on the relevant columns eg: hash(column_a, column_b). Queries can quickly find exact match data by examining only the bucket identified by the hash function. However they cannot easily scan over a subset of the index. For multi-column indexes, query filters must match all of the index columns to be able to take advantage of the index. SingleStore DB supports HASH indexes on both columnstore and rowstore tables. For more information, see USING HASH behavior.
Consider an example table:
CREATE TABLE t(a int, b int, INDEX(a, b) USING HASH);
Suppose we are running queries like:
SELECT * FROM t WHERE a < 3;
EXPLAIN shows us that since we are performing a range scan and not filtering on all the columns in our hash index, a full Table Scan is performed.
mysql> EXPLAIN SELECT * FROM t WHERE a < 3;
+------------------------------------------------+
| EXPLAIN |
+------------------------------------------------+
| Gather partitions:all |
| Project [t.a, t.b] |
| Filter [t.a < 3] |
| TableScan db.t |
+------------------------------------------------+
The hash index is only utilized if the query uses only equality predicates, and filters on all columns in the hash index.
mysql> EXPLAIN SELECT * FROM t WHERE a=3 AND b=7;
+--------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------+
| Gather partitions:all |
| Project [t.a, t.b] |
| IndexRangeScan db.t, KEY a (a, b) USING HASH storage:lf_hashtable scan:[a = 3 AND b = 7] |
+--------------------------------------------------------------------------------------------------------------------------+
A columnstore table cannot have a multi-column HASH index.
Runtime Plan Choosing
SingleStore DB can dynamically select which index to use for a query at runtime. Instead of collecting statistics and building histograms, SingleStore DB can compute these statistics for a given query on-demand by inspecting its indexes. If a query can match more than one index, SingleStore DB compiles an execution plan for each choice, along with the necessary expression logic to cheaply analyze and evaluate which plan to choose at runtime. This process eliminates the need to manually recompute statistics on indexes.
Index Hints
SingleStore DB supports the following index hint syntax:
tbl_name [index_hint]
index_hint:
USE {INDEX | KEY} (index_list)
| IGNORE {INDEX | KEY} (index_list)
| FORCE {INDEX | KEY} (index_list)
index_list:
index_name [, index_name] ...
USEandFORCEhints force the use of one of the specified indexes to run the query. In SingleStore DB, there is no difference between aUSEandFORCEhint.IGNOREhints disallow the specified indexes from being used to run the query.
The EXPLAIN <query> statement can be used to show which indexes the query considers and which one it will actually use.
Index Commands
Related Commands
An index may also be specified when creating or altering tables.
Related Topics
- Training: SingleStore DB Indexes
