PolarDB:Create IMCIs when you create a table

Prerequisites

Before you begin, ensure that you have:

• Read-only column store nodes added to your cluster. See Add a read-only column store node

• A cluster endpoint configured for request distribution. See Request distribution overview

• A connection to the cluster through the cluster endpoint. See Connect to a cluster

How IMCI creation works across CREATE TABLE variants

All three methods use the COLUMNAR=1 annotation in the COMMENT field. The table below summarizes the scope and limitations of each method so you can choose the right one before writing your DDL statement.

Create an IMCI with CREATE TABLE

Add COLUMNAR=1 to the COMMENT field of a specific column to index that column, or add it to the table-level COMMENT to index all supported columns at once.

Index specific columns:

CREATE TABLE t1(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

This creates IMCIs on col1 and col2. The col3 column has no IMCI.

Index all columns:

CREATE TABLE t2(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB COMMENT 'COLUMNAR=1';

This creates IMCIs on all columns whose data types support it — in this case, col1, col2, and col3.

COLUMNAR is not case-sensitive. No delimiter is required between COLUMNAR=1 and any existing COMMENT content, and the annotation does not affect that existing content.

Create an IMCI with CREATE TABLE LIKE

When a source table has an IMCI, the destination table automatically inherits the same IMCI — no additional annotation is needed.

-- If source_table has an IMCI, dest_table inherits the same IMCI.
CREATE TABLE dest_table LIKE source_table;

Create an IMCI with CREATE TABLE ... SELECT

Add COLUMNAR=1 to the table-level COMMENT to create IMCIs on all columns of the new table, including any columns added later. Per-column IMCI creation is not supported with this syntax.

CREATE TABLE t3(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB;

CREATE TABLE t4 COMMENT 'COLUMNAR=1' SELECT col1, col2 FROM t3;

t4 gets IMCIs on col1, col2, and any columns added afterward.

Verify the IMCI structure

Run SHOW CREATE TABLE <tablename> FULL to confirm that IMCIs were created. The standard SHOW CREATE TABLE only shows the COMMENT field setting — it does not show IMCI details.

SHOW CREATE TABLE test.t2;

Output — IMCI details are not shown:

*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `col1` int(11) DEFAULT NULL,
  `col2` datetime DEFAULT NULL,
  `col3` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='COLUMNAR=1'

SHOW CREATE TABLE test.t2 FULL;

Output — IMCI details appear in the COLUMNAR INDEX field:

*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `col1` int(11) DEFAULT NULL,
  `col2` datetime DEFAULT NULL,
  `col3` varchar(200) DEFAULT NULL,
  COLUMNAR INDEX (`col1`,`col2`,`col3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='COLUMNAR=1'

What's next

• To add IMCIs to existing tables, see the ALTER TABLE documentation.

• To manage and monitor IMCIs, see the IMCI management overview.