Accelerate High-Concurrency Point Queries via Row Store Mode - ApsaraDB for SelectDB

ApsaraDB for SelectDB uses column store by default. For wide tables, this amplifies random read I/O when retrieving a single row, slowing down point queries. The frontend (FE) is written in Java, so parsing large volumes of concurrent SQL queries generates high CPU overhead. To address these issues, SelectDB provides three complementary mechanisms—row store, short query path, and prepared statements—that significantly reduce latency for high-concurrency point queries.

How it works

Mechanism	What it does
Row store	Stores a copy of each row in row format, eliminating column-by-column I/O for point queries
Short query path	Lets the query optimizer bypass the standard execution engine and resolve primary-key lookups with a single remote procedure call (RPC)
Prepared statements	Caches parsed SQL and expressions at the session level in the FE, removing repeated parsing overhead from high-concurrency workloads

When to use row store

Row store consumes additional storage space. Use the following table to decide whether to enable it.

Scenario	Recommended storage	Recommendation
High-concurrency primary-key lookups	Row store	Enable row store + merge on write (MoW)
Complex aggregations and multi-table JOINs on wide tables	Column store	Keep default column store
Mixed workloads (point queries + analytics)	Both	Enable row store selectively

Enable row store

Row store must be enabled at table creation time. Add the following property to the CREATE TABLE statement:

"store_row_column" = "true"

Row store cannot be enabled or disabled after a table is created.

Optimize point queries in the Unique Key model

When both enable_unique_key_merge_on_write and store_row_column are set to true on a Unique Key model table, the query optimizer activates a short query path for primary-key point queries. Only one RPC is required to execute such a query.

The following example creates a table with row store and MoW enabled:

CREATE TABLE `tbl_point_query` (
    `key` int(11) NULL,
    `v1` decimal(27, 9) NULL,
    `v2` varchar(30) NULL,
    `v3` varchar(30) NULL,
    `v4` date NULL,
    `v5` datetime NULL,
    `v6` float NULL,
    `v7` datev2 NULL
) ENGINE=OLAP
UNIQUE KEY(`key`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`key`) BUCKETS 16
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "light_schema_change" = "true",
    "store_row_column" = "true"
);

Property	Value	Purpose
`enable_unique_key_merge_on_write`	`true`	Enables MoW so the storage engine can quickly locate rows by primary key
`store_row_column`	`true`	Enables row store for fast full-row retrieval
`light_schema_change`	`true`	Required for the short query path; the optimizer uses the unique column ID from lightweight schema change to locate columns

The short query path applies only when the WHERE clause contains equality conditions on key columns of a single table, for example:

SELECT * FROM tbl_point_query WHERE key = 123;

Use prepared statements

Prepared statements are fully compatible with the MySQL protocol. When enabled in the FE, SelectDB parses each SQL statement and its expressions once, then caches the result in session-level memory. Subsequent executions reuse the cached objects, skipping parsing entirely. For CPU-bound primary-key point queries, this can improve throughput by more than four times.

Prepared statements work only for point queries based on primary keys.

Enable prepared statements via Java Database Connectivity (JDBC):

Add useServerPrepStmts=true to the JDBC URL:

jdbc:mysql://127.0.0.1:9030/ycsb?useServerPrepStmts=true

Prepare the statement once and reuse the PreparedStatement object across queries:

// Use ? as a placeholder. Reuse readStatement across multiple queries.
PreparedStatement readStatement = conn.prepareStatement(
    "SELECT * FROM tbl_point_query WHERE key = ?"
);

readStatement.setInt(1, 1234);
ResultSet resultSet = readStatement.executeQuery();

// Reuse the same statement for the next query
readStatement.setInt(1, 1235);
resultSet = readStatement.executeQuery();

Enable row cache

SelectDB includes a page cache that stores data column by column—each page holds one column's data. In row store mode, a single page holds data from multiple columns, which makes it more vulnerable to eviction by large analytical queries, reducing cache hit rates.

Row cache addresses this by maintaining a dedicated row-level cache that uses the least recently used (LRU) eviction policy, keeping frequently accessed rows in memory even when large analytical queries compete for cache space.

Configure row cache in the backend (BE) settings:

Parameter	Default	Description
`disable_storage_row_cache`	`false`	Controls row cache availability. Defaults to `false`, which keeps row cache enabled. Set to `true` to disable row cache and reduce memory overhead.
`row_cache_mem_limit`	`20`	Maximum percentage of memory allocated to row cache. Default is 20%.

Limitations

Row store can only be enabled at table creation time. Modifying an existing table is not supported.
The short query path applies only to equality conditions (=) on key columns of a single table. JOIN queries and nested subqueries are not supported.
The WHERE clause must reference only key columns (key-value pair queries).
Prepared statements work only for primary-key point queries.
light_schema_change = true is required for the short query path to function correctly.