KDBS
  1. Docs
  2. SQL reference

CREATE INDEX, DROP INDEX

v3.0.0

Add a secondary index to speed up lookups on non-key columns.

In this article

A secondary index speeds up lookups by columns other than the primary key. In KDBS an index only makes reads faster; it never adds a rule that can make a write fail.

Syntax

CREATE INDEX <index_name> ON <table> (<column> [, ...]) [COVERING (<column> [, ...])]
DROP INDEX <index_name>

Arguments

ArgumentDescription
<index_name>Name of the index.
<table>The table to index.
(<column>, ...)Key columns, up to 4.
COVERING (<column>, ...)Extra columns stored in the index, up to 8, so a lookup can read them without visiting the table.

Examples

CREATE INDEX ix_passenger_name ON passengers(name) COVERING (member);

CREATED INDEX name=ix_passenger_name on=passengers index_oid=2 root_page=145 key_width=33 entry_width=42 entries=0

SHOW INDEXES;

indexes=2
index_oid=1 name=ix_tickets_passenger rel=tickets keys=(passenger_id) covering=(flight_id,baggage_kg) root_page=142 key_width=9 entry_width=33 height=1 entries=9
index_oid=2 name=ix_passenger_name rel=passengers keys=(name) covering=(member) root_page=145 key_width=33 entry_width=42 height=1 entries=8

DROP INDEX ix_passenger_name;

The sample tickets table already has the index ix_tickets_passenger, on passenger_id.

How it works

  • Each entry holds the key columns, the covered columns and the row’s primary key.
  • A WHERE column = value on a non-key column uses an index first, then a Cabin, then a full scan, in that fixed order. The choice depends on the statement and the catalog, never on data statistics.
  • Index maintenance only appends. DELETE does not touch the index; every read checks entries against the row, so a stale entry is skipped.
  • CREATE INDEX and DROP INDEX wait for open writes and running reads of the table, and hold new writes until they finish. A wait longer than lock_wait_fault_net_ms (default 1 second) is refused with a retryable TXN_CONFLICT.
  • Both are transactional: inside a transaction, ROLLBACK undoes them.

Limitations

  • CREATE UNIQUE INDEX is refused (NOT_IMPLEMENTED). Use the primary key or an assertion for uniqueness rules.
  • There are no index-only scans; COVERING saves the visit to the table for the covered columns, nothing more.
  • Key and covered columns cannot be nullable.
  • More than 4 key columns or 8 covered columns is refused, never truncated.

See also