Porting Tables to SingleStore

Shard keys

An important decision to make when porting your schema is setting up the appropriate shard keys, which determine how rows are partitioned (see Sharding for more details).

You can choose how to shard a table by adding a SHARD KEY. This functions like a normal table index, and can contain any number of columns. For example, the following table is hash partitioned based on the values of a and b - all rows with the same values for both a and b will be on the same partition:

CREATE TABLE t1(a INT, b INT, c INT, SHARD KEY(a, b));

If your table has a PRIMARY key, SingleStore will by default choose that key as the shard key. For example, both of the following table definitions will shard the table by a:

CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a));

CREATE TABLE t1(a INT, b INT, SHARD KEY(a), PRIMARY KEY(a));

If your table does not have a primary key, then SingleStore will by default shard rows uniformly across the partitions. You can also choose to shard your table this way by entering a blank shard key. For example, both of the following will shard the table this way:

CREATE TABLE t1(a INT, b INT);

CREATE TABLE t1(a INT, b INT, SHARD KEY());

If you aren’t sure yet what your workload will look like, these defaults will allow you to continue developing your application and do not impose any functional restrictions. Once you understand how your workload performs in SingleStore better, you can recreate your table with a different chosen shard key.

Choosing a Shard Key

There are two major factors to take into account when defining a shard key:

• Using a column or set of columns unique enough to minimize skew.

• Sharding on columns which you expect to filter or join on often. This allows the optimizer to minimize network traffic during the execution of the query (see Distributed DML Query Execution for more details).

Let’s say we have the following table:

CREATE TABLE events(event_id INT, user_id INT, time TIMESTAMP);

If we aren’t sure yet what our workload will look like, just using the first create table statement without specifying a shard key will shard rows uniformly. This will allow you to get started. It will ensure that there is no skew, but may not yield optimal query performance.

Let’s say we have this query which we would like to be extremely performant:

SELECT COUNT(DISTINCT first.user_id)
FROM events AS first JOIN events AS second
WHERE first.user_id = second.user_id
    AND TIMESTAMPDIFF(MINUTE, first.time, second.time) < 20;

In this case, we’ll want to modify our table schema to shard on user_id. When we run our query, we’ll be able to take advantage of locality when performing the join and when computing the distinct aggregate value: The advantage with the join is that it will happen on the leaves without having to repartition or move data between leaves.

CREATE TABLE events(event_id INT, user_id INT, time TIMESTAMP, SHARD KEY(user_id));