Creating a Columnstore Table

KEY(<column name>) is equivalent to KEY(<column name>) USING HASH. For example,

CREATE TABLE t(a INT, b INT, KEY(a));

is equivalent to:

CREATE TABLE t(a INT, b INT, SORT KEY(), KEY(a) USING HASH);

KEY(<column 1 name>,<column 2 name>, ... <column n name>) is equivalent to KEY(<column 1 name>,<column 2 name>,... <column n name>) USING HASH. For example,

CREATE TABLE t(a INT, b INT, c INT, KEY(a,b));

is equivalent to:

CREATE TABLE t(a INT, b INT, SORT KEY(), KEY(a,b) USING HASH);

Using multi-column unique keys in a columnstore table is supported starting in engine version 7.5.

A query against t with an equality filter on a, an equality filter on b, or equality filters on both a and b could benefit from KEY(a,b) USING HASH. A query that uses both equality filters would be the most efficient.

Unlike other database products, SingleStoreDB utilizes per-segment inverted indexes and cross-segment indexes to support multi-column keys while minimizing storage costs.

Each secondary index contains a collection of multiple cross-segment indexes, each of which is a hash table covering one or more segments. The background merger works to merge the hash tables to reduce the number of lookups necessary for a seek operation.

These data structures work together to accomplish these fast seeks as follows:

Columnstores (as compared to rowstores) are optimized for analytical processing. With this schema, you can more quickly answer analytical questions such as:

You can utilize the Universal Storage capabilities to perform transaction processing operations on the tables in the schema. For example:

CREATE REFERENCE TABLE accounts(
    id INT NOT NULL AUTO_INCREMENT,
    name TEXT,
    UNIQUE KEY `PRIMARY` (id) USING HASH,
    UNIQUE KEY(name) USING HASH,
    SORT KEY()
);

CREATE TABLE users (
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    user_name TEXT,
    account_id BIGINT,
    SHARD(id),
    UNIQUE KEY `PRIMARY` (id) USING HASH,
    SORT KEY()
);

CREATE TABLE webpages(
    id BIGINT NOT NULL AUTO_INCREMENT,
    url_host BLOB,
    url_path BLOB,
    SHARD(id),
    UNIQUE KEY `PRIMARY` (id) USING HASH,
    KEY(url_host) USING HASH,
    SORT KEY()
);

CREATE TABLE webpage_visits(
	id BIGINT(20) NOT NULL AUTO_INCREMENT,
	user_id BIGINT(20),
	page_id INT(11),
	when_clicked DATETIME,
	SHARD(id),
	UNIQUE KEY `PRIMARY` (id) USING HASH,
	KEY(user_id, page_id) USING HASH,
	KEY (when_clicked) USING HASH,
	SORT KEY()
);

If you intend to query webpage_visits using a range filter on when_clicked, you could have made when_clicked the sort key. You could have done this using the following standard CREATE TABLE syntax.

CREATE TABLE webpage_visits(
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT,
    page_id INT,
    when_clicked DATETIME,
    PRIMARY KEY (id),
    KEY (user_id, page_id),
    SORT KEY (when_clicked)
);

The query engine would internally convert the standard CREATE TABLE syntax to equivalent columnstore-specific CREATE TABLE syntax, as follows:

CREATE TABLE webpage_visits(
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT,
    page_id INT,
    when_clicked DATETIME,
    UNIQUE KEY `PRIMARY` (id) USING HASH,
    KEY (user_id) USING HASH,
    KEY (page_id) USING HASH,
    SORT KEY (when_clicked) 
);