Getting started · a normal CREATE TABLE in MySQL

ENGINE=NDB makes a table a RonDB table

CREATE TABLE t1 (a INT) ENGINE=NDB;
-- NDBCLUSTER is a synonym; both are
-- case insensitive

docs: basic_sql.md (Simplest form of table) · code: ha_ndbcluster.cc (SDI as extra metadata)

Primary keys · not optional in RonDB

A table without a primary key gets a hidden one

CREATE TABLE t1 (a INT) ENGINE=NDB;

-- what the data nodes store:
--   a     INT
--   $PK   BIGINT UNSIGNED, auto-increment,
--         hidden from SQL

docs: basic_sql.md · code: storage/ndb/plugin/ha_ndbcluster.cc (the $PK column)

Indexes · what a plain PRIMARY KEY creates

A primary key gives a distributed hash index and an ordered index

CREATE TABLE t1 (
  a INT PRIMARY KEY
) ENGINE=NDB;

Why both? Almost everyone expects an index to be ordered. The base access method is the hash index, so RonDB adds an ordered index to match that expectation. Primary key columns can never be NULL.

docs: basic_sql.md (Simple table with primary key)

USING HASH · only what you need

USING HASH drops the ordered index

CREATE TABLE t1 (
  a INT,
  PRIMARY KEY (a) USING HASH
) ENGINE=NDB;

Use it when the table is only read by key. Keep the default when the application scans ranges of the primary key.

docs: basic_sql.md (Simple table with only primary key hash index, Index sizes)

Secondary indexes · KEY and INDEX

Secondary indexes: a normal index is an ordered index, local to each fragment

CREATE TABLE t1 (
  a INT, b INT,
  PRIMARY KEY (a) USING HASH,
  KEY (b)
) ENGINE=NDB;

Each fragment has its own tree, so a scan on b visits every partition unless the query also fixes the partition key (module 04).

docs: basic_sql.md (Simple table with primary key hash index and an index, Indexes)

Unique indexes · a hidden table, kept in sync by triggers

Unique indexes: a unique key is a table of its own

docs: basic_sql.md (Simple table with primary key hash index and a unique index) · code: ha_ndbcluster.cc ("$unique" suffix)

ndb_desc · what the data nodes hold for a table

ndb_desc: a table seen from the data nodes' side

code: mysql-test/suite/ndb/r/ndb_desc_extra.result (create table t1 (a int auto_increment primary key, b int) engine=ndb) · lines merged, values unchanged · the test cluster runs PartitionsPerNode=4, hence 8 partitions on 2 nodes

AUTO_INCREMENT · many writers at once

Auto-increment values come in ranges, one range per MySQL server

CREATE TABLE t1 (
  a BIGINT AUTO_INCREMENT,
  PRIMARY KEY (a) USING HASH
) ENGINE=NDB;

-- my.cnf, per MySQL server
ndb-autoincrement-prefetch-sz = 512

docs: basic_sql.md (A simple table with a generated primary key) · code: ha_ndbcluster.cc (autoincrement_prefetch_sz: default 512, 1–65535)

Index memory · independent of the indexed columns

Index memory: an index stores row addresses, never key values

The primary key costs 15 B in its hash index plus 10 B in its default ordered index; one ordered secondary index adds 10 B: about 35 bytes of index memory per row, before the row itself. Module 08 opens the index structures.

docs: basic_sql.md (Index sizes)

Row memory · what a row costs besides its values and its indexes

Row overhead: about 34 bytes per row in a typical table, before any column value

Typical table: 16 + 4 + 8 + 4 + 2 = 34 bytes, plus 2 per VARCHAR, padded to whole words. Add the index memory, then multiply by NoOfReplicas.

code: Dbtup.hpp (Tuple_header, Var_part_ref) · tuppage.hpp · DbtupExecQuery.cpp (shrink_tuple) · DbtupMeta.cpp · ha_ndbcluster.cc (ForceVarPart) · layout in module 07

Limits · in RonDB 26.10

Table limits: how big a table and a row can be

code: ndb_limits.h (MAX_ATTRIBUTES_IN_*, MAX_*_SIZE_IN_WORDS; MAX_VAR_SIZE_IN_WORDS and MAX_DISK_VAR_SIZE_IN_WORDS 8158) · ndbapi_limits.h (NDB_MAX_KEY_SIZE) · sql_const.h (MAX_KEY_LENGTH) · handler.h (max_key_length: the smaller) · CMakeLists.txt (MAX_INDEXES) · BLOBs: module 04

Columns · any MySQL type, with a few index limits

Column types and character sets

The default character set is utf8mb4: up to 4 bytes per character. Set a smaller one per table or per column when the data allows it.

docs: basic_sql.md (More complex tables, Indexes)

Schema objects · created together, or not at all

Schema objects: what one CREATE TABLE creates in the data nodes

docs: rondb_schema_transaction.md (Transaction Steps) · foreign_keys.md · code: ha_ndbcluster.cc · storage/ndb/src/ndbapi/NdbBlob.cpp (NDB$BLOB names)

Schema transactions · slow, but very reliable

Schema transactions: a change commits or rolls back as a unit, through node failures

docs: rondb_schema_transaction.md · code: dbdict (the log file is P0.SchemaLog)

Schema distribution · every MySQL server learns about the table

Schema distribution: RonDB pushes the definition to every MySQL server

code: ha_ndbcluster.cc (SDI as extra metadata) · storage/ndb/plugin/ndb_schema_dist_table.cc (mysql.ndb_schema, event REPL$mysql/ndb_schema)

MySQL objects · where each one lives

Tables are cluster-wide; views, triggers and procedures are per server

docs: mysql_concepts_in_ndb.md · code: storage/ndb/plugin/ndb_stored_grants.cc (NDB_STORED_USER)

Transactions · what MySQL features carry over

Every statement runs as a distributed transaction

docs: basic_sql.md (Writing to a table, Transactions) · mysql_concepts_in_ndb.md (Savepoints, Table locks, XA support)

Housekeeping · three statements that behave differently

COUNT(*), ANALYZE TABLE and OPTIMIZE TABLE

docs: basic_sql.md (SELECT COUNT(*) queries, Optimize/Analyze Table)

Checklist · before a table goes into production

Checklist: eight habits for RonDB tables

1. Define a primary key; avoid the hidden $PK.
2. Use USING HASH when the key is never scanned by range.
3. Make auto-increment keys BIGINT; raise the prefetch size for heavy inserts.
4. Pick a character set per column when utf8mb4 is not needed.
5. Remember every index costs 10–15 bytes per row.
6. Create tables through a MySQL server.
7. Keep MySQL triggers out of your constraints: other APIs bypass them.
8. Run ANALYZE TABLE after loading data.

docs: basic_sql.md · mysql_concepts_in_ndb.md

Recap · module 03

What to take away

• ENGINE=NDB, and always a primary key: otherwise a hidden $PK.
• A primary key is a hash index plus an ordered index; USING HASH keeps only the hash.
• Unique keys are hidden tables kept in sync by triggers.
• Indexes store row addresses, not keys: about 15 bytes per row for hash, 10 for ordered.
• A CREATE TABLE is a schema transaction: all objects or none, distributed to every MySQL server.

Next · 04 · Table options, partitioning, disk columns and BLOBs

RonDB training · 25 modules · you are at 03

Next · 04 · Table options