You are viewing an older version of this section. View current production version.
Table
There are three types of tables in MemSQL: reference tables, sharded tables, and temporary tables.
Reference Tables
Reference tables are relatively small tables that do not need to be distributed and are present on every node in the cluster. They are both created and written to on the master aggregator. Reference tables are implemented via primary-secondary replication to every node in the cluster from the master aggregator. Replication enables reference tables to be dynamic: updates that you perform to a reference table on the master aggregator are quickly reflected on every machine in the cluster.
MemSQL aggregators can take advantage of reference tables’ ubiquity by pushing joins between reference tables and a distributed table onto the leaves. Imagine you have a distributed clicks
table storing billions of records and a smaller customers
table with just a few million records. Since the customers
table is small, it can be replicated on every node in the cluster. If you run a join between the clicks
table and the customers
table, then the bulk of the work for the join will occur on the leaves.
Reference tables are a convenient way to implement dimension tables.
Sharded Tables
Every database in the distributed system is split into a number of partitions. Each sharded table created is split with hash partitioning over the table’s primary key; a portion of the table is stored in each of its database’s partitions.
Partitions are implemented as databases on the leaves. For example, partition 3 of database db
is stored in a database db_3
on one of the leaves. For every sharded table created inside db
, a portion of it’s data will reside in db_3
on that leaf.
Although sharded tables in the same database share the same database containers on the leaves, no assumptions can be made about particular rows from different tables being co-located on a partition.
Temporary Tables
Temporary tables exist, storing data in memory, for the duration of a client session. MemSQL does not write logs or take snapshots of temporary tables. Temporary tables are designed for temporary, intermediate computations in business intelligence and data science.
Unlike CREATE TABLE without the TEMPORARY
option, CREATE TEMPORARY TABLE
can be run on any aggregator - not just the master aggregator. Temporary tables are sharded tables, and can be modified and queried like any “permanent” table, including distributed joins.
Views cannot reference temporary tables because temporary tables only exist for the duration of a client session. Although MemSQL does not materialize views, views are available as a service for all clients, and so cannot depend on client session-specific temporary tables. Temporary tables do not support ALTER TABLE
.
Table Commands
- CREATE TABLE
- SHOW TABLES
- SHOW COLUMNS
- ALTER TABLE
- DESCRIBE
- SHOW TABLE STATUS
- FLUSH TABLES
- LOCK TABLES
- UNLOCK TABLES
- DROP TABLE