With a number of exciting features, it is always important for any database to perform better, while simultaneously delivering a better user experience. Indexes are the key objects in any database system, providing path and metadata to the optimizer. SingleStore works on unique indexes, which are best on different table types.
This is the second article in a three-part series on using objects in SingleStore. In case you missed it, check out part one here to explore using table types.
Types of Indexes:
SingleStore allows you to create indexes for search paths like other standard databases. Indexes are generally used by optimizers for filter (WHERE clause) and JOIN conditions. Main indexes include skiplist for Rowstore tables, and clustered Columnstore for Columnstore tables.
SingleStoreDB supports skiplists only on rowstore tables. These are similar to B-tree indexes. Skiplists are optimized to run in memory, support lock-free implementation and offer extremely fast insert performance.
Clustered columnstore indexes provide significant data compression and are backed by disk. Currently, clustered columnstore indexes cannot be combined with in-memory row store indexes on the same table.
Five key advantages of Columnstore index / SORT KEY include:
These are classic indexes on rowstore and columnstore tables. A HASH index will only be utilized if the query employs an equality filter for every column in the index. For multi-column indexes, query filters must match all of the index columns to take advantage of the index. It can be created with a clause using HASH.
If a rowstore or columnstore table is being created, the following applies:
If a columnstore table is being created, the following applies:
Full-text search enables you to search for words or phrases in a large body of text through an inverted index. These types of indexes can be defined during the CREATE TABLE statement on columnstore tables. Full-text indexes cannot be altered or dropped, and can only be created on char, varchar, text or longtext.
Indexing JSON Data:
SingleStore enables storage of JSON data, as well as quickly querying the data using indexing on persisted columns. JSON columns are not indexed directly — they are indexed using computed columns. For the fastest performance, you should not use JSON built-ins or :: notation in your filters. Instead, create a computed column that includes the JSON column in the computation, and then use the computed column for the index. This way, the index gets updated only when the relevant JSON data is updated in a row.
CREATE TABLE assets ( tag_id BIGINT PRIMARY KEY, name TEXT NOT NULL, description TEXT, properties JSON NOT NULL, weight AS properties::%weight PERSISTED DOUBLE, license_plate AS properties::$license_plate PERSISTED TEXT, KEY(license_plate), KEY(weight));
Don’t miss part three of this series, where I’ll dive into additional details about procedural objects.
Interested in seeing how objects in SingleStore work? Try it out free today.