PolarDB:Create and use a CCI

Prerequisites

Before you begin, make sure your instance meets the following requirements:

• Edition: Enterprise Edition with an AUTO mode database

• Version: 5.4.19-16989811 or later

For version naming rules, see Release notes. To check your current version, see View and update the version of an instance.

Usage notes

• Prefix indexes cannot be used on a CCI.

• You must specify an index name when creating a CCI.

• A CCI includes all columns of the primary table by default and is automatically updated when columns are added or dropped. You cannot manually change which columns are included.

• Creating a CCI does not create any additional local indexes.

• The LENGTH parameter of the sort key is ignored in the index definition.

• Primary instances, read-only instances, and columnstore read-only instances all support SHOW INDEX and related query commands. See SHOW COLUMNAR INDEX, SHOW COLUMNAR OFFSET, and SHOW COLUMNAR STATUS.

• Only columnstore read-only instances support CCI-based query execution.

Syntax

PolarDB-X extends MySQL Data Definition Language (DDL) syntax. Use the following syntax to create a CCI the same way you create a regular index in MySQL:

CREATE
    CLUSTERED COLUMNAR INDEX index_name
    ON tbl_name (index_sort_key_name,...)
    [partition_options]

-- Define a partitioning policy
partition_options:
    PARTITION BY
          HASH({column_name | partition_func(column_name)})
        | KEY(column_list)
        | RANGE({column_name | partition_func(column_name)})
        | RANGE COLUMNS(column_list)
        | LIST({column_name | partition_func(column_name)})
        | LIST COLUMNS(column_list)
    partition_list_spec

-- Define a partitioning function
partition_func:
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH

-- Define a partition list
partition_list_spec:
    hash_partition_list
  | range_partition_list
  | list_partition_list

-- For HASH/KEY partitioned tables
hash_partition_list:
    PARTITIONS partition_count

-- For RANGE/RANGE COLUMNS partitioned tables
range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]

-- For LIST/LIST COLUMNS partitioned tables
list_partition_list:
    list_partition [, list_partition ...]

list_partition:
    PARTITION partition_name VALUES IN (value_list) [partition_spec_options]

Create a CCI

The following example creates a table named t_order and a CCI named cc_i_seller on it.

Step 1: Create the primary table.

CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by hash(`order_id`) partitions 16;

Step 2: Create the CCI.

CREATE CLUSTERED COLUMNAR INDEX `cc_i_seller` ON t_order (`seller_id`) partition by hash(`order_id`) partitions 16;

The annotated diagram below shows each component of the CCI definition:

SHOW COLUMNAR INDEX FROM t_order;

SHOW DDL;

Use a CCI in queries

After you create a CCI, you can specify the index table to use for queries in the following ways:

Start with automatic selection. Use hints only when you need to override the optimizer's decision.

USE INDEX({index_name},...)

SELECT t_order.id, t_order.order_snapshot
FROM t_order USE INDEX(cc_i_seller)
WHERE t_order.seller_id = 's1';

IGNORE INDEX({index_name},...)

SELECT t_order.id, t_order.order_snapshot
FROM t_order IGNORE INDEX(cc_i_seller)
WHERE t_order.seller_id = 's1';

FORCE INDEX({index_name})

SELECT a.*, b.order_id
FROM t_seller a
  JOIN t_order b FORCE INDEX(cc_i_seller) ON a.seller_id = b.seller_id
WHERE a.seller_nick = 'abc';

Limitations

SET [GLOBAL] forbid_ddl_with_cci = [true | false];

-- Allow modifying critical columns of a CCI
SET ENABLE_MODIFY_CCI_CRITICAL_COLUMN = TRUE;
-- Rebuild strategy: 0 lets the system select automatically
SET REBUILD_CCI_STRATEGY = 0;
-- Maximum number of CCIs per table; increase if the table already has multiple CCIs
SET MAX_CCI_COUNT = 2;

FAQ

Can I create a CCI without specifying a sort key?

No. You must specify the sort key explicitly in the CREATE CLUSTERED COLUMNAR INDEX statement. The sort key and partition key can be different columns — for example, you can use seller_id as the sort key and order_id as the partition key on the same table.

Can I create a CCI without specifying a partition key?

Yes. If you omit the partition key, PolarDB-X uses the primary key as the partition key with HASH partitioning by default.

How do I check CCI creation progress?

Run SHOW COLUMNAR INDEX to see the current status and SHOW DDL to track DDL task progress. For details on reading the output, see SHOW COLUMNAR INDEX and SHOW DDL.

How do I delete a CCI?

Use the DROP INDEX statement.