Creating a Columnstore Table

Single-Column Keys

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);

Multi-Column Keys

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, SingleStore 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:

• The inverted index is built to map values of an index column to a posting list that stores row offsets within the segment with the value searched. The posting list logs which segment a specific value is located and uses row offsets to determine the value is located.

  

• The cross-segment indexes map values of each indexed column with the ID of the segment where the searched value is located. The cross-segment indexes also map to the starting location of the corresponding postings list in the inverted index for each segment. Lastly, for a multi-column index like the example above (KEY(a,b) USING HASH)), another collection of cross-segment indexes are built on the row of indexed columns to map from the hash of each tuple (value_a and value_b) to the starting locations of the corresponding per-segment postings lists for value_a and value_b.

  

Analytical Processing

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

• How many visits occurred within a particular time period?

• On average, how many visits are there per user?

• On average, how many users are there per account?

• How many unique webpages were clicked?

Transactional Processing

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

• To find all of the webpage visits containing a particular hostname, you can run a query such as SELECT * FROM webpages WHERE url_host = 'myexample.com';. This query allows you take advantage of the hash index on url_host, which allows the query to run faster than if the hash index was not used.

• To find all of the webpages visited on a particular date and time, you can run a query such as SELECT * FROM webpage_visits v JOIN webpages w ON v.page_id = w.id WHERE v.when_clicked = '2020-01-01 16:35:00'; This query does a highly selective join, as there are only a few records in webpage_visits that match the equality filter on when_clicked.

accounts

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()
);

users

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()
);

webpages

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()
);

webpage_visits

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()
);

Querying webpage_visits Using a Range Filter

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) 
);