AnalyticDB:Row store engine

When to use the row store engine

Choose the row store engine when your workload involves:

• High-concurrency point queries: Looking up individual rows by primary key at thousands of queries per second (QPS)

• Real-time reads and writes: Frequent INSERT, UPDATE, and DELETE operations with immediate read-after-write consistency

• Multi-column projections on small result sets: Retrieving a subset of columns for a small number of rows

If your workload is primarily analytical — large scans, aggregations, or joins over millions of rows — use the columnar engine instead.

Prerequisites

Before you begin, ensure that you have:

• An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster

• A resource group created in the cluster with task type Interactive and engine type Serving

• Cluster kernel version 3.2.5 or later

  Note To view or update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

Billing

The row store engine uses pay-as-you-go pricing for both compute and storage:

• Compute: Billed based on the number of AnalyticDB compute units (ACUs) and the unit price of the Serving resource group

• Storage: Billed based on the hot data storage price

For pricing details, see Pricing.

Supported data types

Primary keys support: boolean, tinyint, smallint, int, bigint, varchar, binary, blob, date, time, datetime, timestamp.

Create a row-oriented table

Specify ENGINE = 'row_store' in your CREATE TABLE statement.

Basic example:

CREATE DATABASE test_db;

CREATE TABLE test_db.row_store_table
(
    a int NOT NULL,
    b int,
    c varchar,
    PRIMARY KEY(a)
)
ENGINE = 'row_store'
TABLE_PROPERTIES = '{"row_store_ttl": 1}';

With manually created indexes:

CREATE TABLE test_db.row_store_table
(
    a int NOT NULL,
    b int,
    c varchar,
    PRIMARY KEY(a),
    INDEX c_idx(c),
    INDEX bc_idx(b, c)
)
ENGINE = 'row_store';

With a full-text index on a string column:

CREATE TABLE test_db.row_store_table
(
    a int NOT NULL,
    b int,
    c varchar,
    PRIMARY KEY(a),
    FULLTEXT INDEX c_idx(c)
)
ENGINE = 'row_store';

Data operations

The following DML operations are supported:

Schema changes

Add or remove columns:

-- Add columns
ALTER TABLE test_db.row_store_table
  ADD COLUMN bool boolean,
  ADD COLUMN STR varchar;

-- Remove a column
ALTER TABLE test_db.row_store_table
  DROP COLUMN bool;

Add or remove indexes:

-- Add an index
ALTER TABLE test_db.row_store_table
  ADD INDEX c_idx(c);

-- Remove an index
ALTER TABLE test_db.row_store_table
  DROP INDEX c_idx;

Truncate or drop a table:

TRUNCATE TABLE test_db.row_store_table;

DROP TABLE test_db.row_store_table;

Limits

Performance

On the TPC-H 10 GB dataset, the row store engine delivers over 10x improvement in point query throughput and more than 80% reduction in response time (RT).

PK point query (`WHERE pk = <value>`):

Multiple PK point query (`WHERE pk IN (x, y, z)`):