KDBS
  1. Docs
  2. SQL reference

CREATE CABIN, DROP CABIN

v3.0.0

Track the rows for the values queries actually ask for on a column, without the write cost of a full index.

In this article

A Cabin speeds up column = value lookups like an index does, but it covers only the values that queries have actually asked for. A write pays for a Cabin only when it touches one of those values, so a Cabin suits columns where a few values are looked up often.

For each value a Cabin has observed, it knows every matching row. For a value it has not observed, the lookup falls back to a scan. Dropping part or all of a Cabin can make queries slower, never wrong.

Syntax

CREATE CABIN ON <table> (<column>)
DROP CABIN ON <table> (<column>)

A Cabin has no name of its own; it is identified by its table and column.

Examples

CREATE CABIN ON passengers(name);

CREATED CABIN on=passengers.name cabin_id=1 column=1 observed=0
WARN no recorded access has filtered 'passengers.name'; this cabin will stay empty until traffic probes that column by equality (SHOW ACCESS lists what has been recorded)

SELECT * FROM passengers WHERE name = 'Kim Minjun';   -- the first lookup observes 'Kim Minjun'
SHOW CABINS;
DROP CABIN ON passengers(name);

Column policy

CREATE TABLE can set, per column, who may create a Cabin on it:

PolicyMeaning
CABINCreate a Cabin now.
CABIN AUTOThe engine may decide later.
NO CABINNever, by any route.
(none)Treated as CABIN AUTO.

How it works

  • The first lookup of a value scans the table and records every matching row’s primary key. Later lookups of that value read only those rows.
  • Writes add entries for observed values; nothing is removed on the write path. Each read checks entries against the current row and its visibility, so extra entries cost a little time and never change a result.
  • The engine’s optimizer can create and drop Cabins on CABIN AUTO columns by itself when the server setting cabin_optimizer is on. It is off by default.

Limitations

  • One column per Cabin.
  • Not allowed on the primary key or on a NO CABIN column.
  • Cabin contents are kept in memory. After a restart only the declaration remains, and traffic fills the Cabin again.

See also