You are viewing an older version of this section. View current production version.
Indexes
MemSQL, like all standard SQL databases, allows the creation of indexes on tables, which speed up certain access patterns. As with MySQL, MemSQL supports two index type keywords. The BTREE keyword is for compatibility with MySQL syntax and will create the default MemSQL skip list index instead. The CLUSTERED COLUMNSTORE index type is for columnstore tables.
Skip List Indexes
The default index type in MemSQL is a skip list. Skip lists in MemSQL 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 MemSQL 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.
Column Store Indexes
Column store indexes leverage column store technology to efficiently store and retrieve large number of values from disk (using flash or SSD is recommended). Because column store 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 MemSQL do, they are typically very useful for analytical workloads. MemSQL currently support clustered column store indexes which, when added to a table, will make the entire table structure backed by the column store. Currently column store indexes cannot be combined with in-memory row store indexes on the same table. For more information about using column store indexes in MemSQL, see columnstore
Hash Table Indexes
HASH indexes are intended to provide fast exact-match access to unique values. This means HASH indexes are a good choice for speeding up queries that use equality predicates, such as SELECT * FROM tbl WHERE id=3 OR id=714928.
HASH indexes are not optimized for range scans. We recommend using the default skip list index first, and only consider HASH indexes when there is a demonstrated need and measurable benefit on your particular dataset and workload.
Runtime Plan Choosing
MemSQL can dynamically select which index to use for a query at runtime. Instead of collecting statistics and building histograms, MemSQL can compute these statistics for a given query on-demand by inspecting its indexes. If a query can match more than one index, MemSQL 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
MemSQL 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 MemSQL, 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.
