Basic usage

Sample table

The order detail table orders is used as an example, where id is the order identifier and dt is the order date. The two columns form a composite primary key:

+------------+-------------+---------+----------------+
| TABLE_NAME | COLUMN_NAME |  TYPE   | IS_PRIMARY_KEY |
+------------+-------------+---------+----------------+
| orders     | id          | BIGINT  | true           |
| orders     | dt          | VARCHAR | true           |
| orders     | user_id     | BIGINT  | false          |
| orders     | amount      | DECIMAL | false          |
| orders     | status      | VARCHAR | false          |
+------------+-------------+---------+----------------+

Create a columnstore index

Syntax

CREATE INDEX index_name USING COLUMNAR
ON table_name(column_name(,...))
PARTITION BY ENUMERABLE (
  column_name(,...),
  bucket(bucket_num, column_name)
)
WITH (
  `lindorm_columnar.user.index.database` = 'columnar_db_name',
  `lindorm_columnar.user.index.table`    = 'columnar_tbl_name'
);

Parameters

Example

The following example creates a columnstore index on all columns of the orders table, partitioned by the order date dt with 128 buckets and the bucket partition field as the order identifier id:

CREATE INDEX orders_idx USING COLUMNAR
ON orders(id, dt, user_id, amount, status)
PARTITION BY ENUMERABLE (dt, bucket(128, id))
WITH (
  `lindorm_columnar.user.index.database` = 'my_index_db',
  `lindorm_columnar.user.index.table`    = 'orders_index'
);

Partitioning strategies

The partition expression consists of a regular partition expression and a bucket partition expression, which together determine the number of partitions for the index data.

Regular partition expression

• You can specify zero or more regular partition expressions, separated by commas.

• The expression fields must come from the primary key fields of the wide table.

• Index data is built based on partition values. Queries can leverage partition filtering to efficiently locate data.

• We recommend that the data volume of a single partition be between 50 MB and 512 MB. Use caution when using high-cardinality fields (such as order IDs or user IDs) as regular partition keys, as this can produce a large number of small partitions and cause metadata bloat.

Bucket partition expression

• At least one bucket partition expression must be specified.

• Syntax: bucket(bucket_num, column_name): bucket_num is the number of buckets, and column_name is the bucket partition field.

• Bucket number calculation: hash(column_name) % bucket_num. For example, bucket(128, id) means hash(id) % 128.

• The bucket partition field must be a primary key field and should have sufficient dispersion to avoid data skew.

• We recommend that the number of buckets be ≤ 1024.

View columnstore indexes

After a columnstore index is created, the index data is continuously built. Data synchronization includes full synchronization (backfilling historical data from the wide table) and incremental synchronization (consuming the wide table WAL for continuous appending). After entering the incremental phase, the synchronization latency is typically < 1 hour.

Use the SHOW INDEX statement to view all indexes (including secondary indexes and columnstore indexes) on a specified wide table:

SHOW INDEX FROM orders;

Sample output:

+-------------+------------+------------+-------------+---------------------------------+------------+---------------+---------------------------------+-----------+-------------------------------------------------------------+
| TABLE_SCHEMA| DATA_TABLE | INDEX_NAME | INDEX_STATE | INDEX_PROGRESS                  | INDEX_TYPE | INDEX_COVERED | INDEX_COLUMN                    | INDEX_TTL | INDEX_DESCRIPTION                                           |
+-------------+------------+------------+-------------+---------------------------------+------------+---------------+---------------------------------+-----------+-------------------------------------------------------------+
| default     | orders     | orders_idx | ACTIVE      | 2026-08-10 18:59:20.652 +0800   | COLUMNAR   | NA            | id,dt,user_id,amount,status     |           | index table: my_index_db.orders_index; partition by: [...]  |
+-------------+------------+------------+-------------+---------------------------------+------------+---------------+---------------------------------+-----------+-------------------------------------------------------------+

Field description

Use columnstore indexes

In a SELECT statement, use the hint _use_ldps_(cg_name), _columnar_index_ to route the query to the computing engine and hit the columnstore index.

Large-scale data aggregation

SELECT /*+ _use_ldps_(cg0), _columnar_index_ */
       dt, COUNT(*) AS order_cnt, SUM(amount) AS gmv, MAX(amount) AS max_amount
FROM my_index_db.orders_index
WHERE dt BETWEEN '2026-08-01' AND '2026-08-10'
GROUP BY dt;

Large-scale data sorting

SELECT /*+ _use_ldps_(cg0), _columnar_index_ */
       id, user_id, amount
FROM my_index_db.orders_index
WHERE dt = '2026-08-10' AND status = 'PAID'
ORDER BY amount DESC
LIMIT 100;

Large-scale data join

After creating columnstore indexes for multiple wide tables, you can perform JOIN operations across indexes:

SELECT /*+ _use_ldps_(cg0), _columnar_index_ */ *
FROM my_index_db.orders_index    AS o
JOIN my_index_db.users_index     AS u
  ON o.user_id = u.id
WHERE o.dt = '2026-08-10'
LIMIT 100;

Delete a columnstore index

DROP INDEX orders_idx ON orders;

For the complete syntax of DROP INDEX, see DROP INDEX.