PolarDB:Execute DDL statements to dynamically create and delete an IMCI

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 your request distribution strategy. See Request distribution overview.

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

Supported data types and versions

The following data types are supported based on your PolarDB for MySQL version:

If a table or column already has a comment, prepend COLUMNAR=1 (or COLUMNAR=0) to the existing content. For example, COMMENT 'abc' becomes COMMENT 'COLUMNAR=1abc'.

Create an IMCI

Add COMMENT 'COLUMNAR=1' to an ALTER TABLE statement to create an IMCI. The scope depends on where you add the comment:

• Table-level IMCI — add the comment to ALTER TABLE. The IMCI covers all columns of supported data types.

• Column-level IMCI — add the comment to ALTER TABLE ... MODIFY COLUMN .... The IMCI covers only the specified columns.

If your cluster is connected through Data Management (DMS), do not use the lock-free schema change process to modify the COMMENT field.

Create a table-level IMCI:

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

-- Create an IMCI for the entire table
ALTER TABLE t5 COMMENT 'COLUMNAR=1';

Create a column-level IMCI:

-- Create an IMCI for specified columns only
ALTER TABLE t5 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=1',
               MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=1';

Delete an IMCI

Set COMMENT 'COLUMNAR=0' to remove an IMCI, using the same syntax as creation:

• Table-level — add the comment to ALTER TABLE

• Column-level — add the comment to ALTER TABLE ... MODIFY COLUMN ...

Delete a column-level IMCI:

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

-- Remove the IMCI from specific columns
ALTER TABLE t6 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=0',
               MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=0';

Delete a table-level IMCI:

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

-- Remove the table-level IMCI
ALTER TABLE t7 COMMENT 'COLUMNAR=0';

Modify IMCI definition

Add or remove individual columns from an existing IMCI using ALTER TABLE ... MODIFY COLUMN ...:

• COMMENT 'COLUMNAR=1' — adds the column to the IMCI

• COMMENT 'COLUMNAR=0' — removes the column from the IMCI

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

-- Add col3 to the IMCI
ALTER TABLE t8 MODIFY COLUMN col3 VARCHAR(200) COMMENT 'COLUMNAR=1';

-- Remove col2 from the IMCI
ALTER TABLE t8 MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=0';

Create an IMCI for most columns in a table

For OLAP workloads, tables often have many columns that should be covered by an IMCI. Rather than listing each column individually, set COMMENT 'COLUMNAR=1' at the table level — this covers all columns of supported data types by default — and use COMMENT 'COLUMNAR=0' to exclude specific columns.

Example: Create an IMCI for all columns except col7.

CREATE TABLE t9(
  col1 INT, col2 INT, col3 INT,
  col4 DATETIME, col5 TIMESTAMP,
  col6 CHAR(100), col7 VARCHAR(200),
  col8 TEXT, col9 BLOB
) ENGINE InnoDB;

Run two separate ALTER TABLE statements for better performance:

-- Step 1: Exclude the column that should not be in the IMCI
ALTER TABLE t9 MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0';

-- Step 2: Create the table-level IMCI
ALTER TABLE t9 COMMENT 'COLUMNAR=1';

Combining both operations in a single statement — for example, ALTER TABLE t9 COMMENT 'COLUMNAR=1', MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0' — triggers InnoDB Online DDL's online rebuild mode, which has significantly lower performance. Use two separate statements instead.

After running the statements, verify the result with SHOW CREATE TABLE t9 FULL\G. The output confirms which columns are included in the IMCI:

SHOW CREATE TABLE t9 FULL\G
*************************** 1. row ***************************
      Table: t9
Create Table: CREATE TABLE `t9` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  `col4` datetime DEFAULT NULL,
  `col5` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `col6` char(100) DEFAULT NULL,
  `col7` varchar(200) DEFAULT NULL COMMENT 'COLUMNAR=0',
  `col8` text,
  `col9` blob,
  COLUMNAR INDEX  (`col1`,`col2`,`col3`,`col4`,`col5`,`col6`,`col8`,`col9`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'COLUMNAR=1'

The col7 column is excluded from the IMCI, as shown by its COMMENT 'COLUMNAR=0' and its absence from the COLUMNAR INDEX definition.

Create an IMCI when adding columns

When adding new columns with ALTER TABLE ADD COLUMN, include COMMENT 'COLUMNAR=1' to add those columns to the IMCI automatically.

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

-- Add col4 and include it in the IMCI
ALTER TABLE t10 ADD col4 DATETIME DEFAULT NOW() COMMENT 'COLUMNAR=1';

Use INSTANT DDL with IMCI tables

The behavior of INSTANT DDL when adding or dropping columns on a table with an IMCI depends on your PolarDB for MySQL version.

For versions earlier than 8.0.1.1.42 and 8.0.2.2.23:

INSTANT DDL is disabled by default for IMCI tables. Without INSTANT DDL, adding or dropping a column requires rebuilding the IMCI. The IMCI remains available during the rebuild.

To enable INSTANT DDL, use one of the following methods:

• Run the following statement in your database:

  SET imci_enable_add_column_instant_ddl = ON

• In the PolarDB console, go to the Parameters page and set loose_imci_enable_add_column_instant_ddl to ON.

After enabling INSTANT DDL, the IMCI is rebuilt asynchronously in the background when you add or drop columns. The table-level IMCI is temporarily unavailable until the rebuild completes. Read-only row store node performance is not affected.

For versions 8.0.1.1.42 or later and 8.0.2.2.23 or later:

INSTANT DDL is enabled by default for IMCI tables. This mode is not compatible with the original rebuild mode. To use the rebuild mode, set imci_enable_add_column_instant_ddl to OFF and ensure the table has a primary key.

Check IMCI build progress

IMCI creation runs as an asynchronous DDL operation. After the DDL statement completes on the primary node, metadata changes are replicated to the read-only column store node via Redo logs, and background threads start building the IMCI concurrently.

During this period:

• OLAP queries run on the read-only row store node (IMCI not yet available)

• After the IMCI is fully built, OLAP queries switch to the read-only column store node

To check whether the IMCI is ready, query INFORMATION_SCHEMA.IMCI_INDEXES on the read-only column store node.

Example:

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

ALTER TABLE t11 COMMENT 'COLUMNAR=1';

-- Check IMCI build state
SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES WHERE TABLE_NAME = 't11';

For partitioned tables, use a LIKE pattern:

SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES WHERE TABLE_NAME LIKE '%t1%';

Sample output:

+--------+-----------+----------+--------+---------+------+----------+--------+
|TABLE_ID|SCHEMA_NAME|TABLE_NAME|NUM_COLS|PACK_SIZE|ROW_ID|STATE     |MEM_SIZE|
+--------+-----------+----------+--------+---------+------+----------+--------+
|    xxxx| test      | t11      |       3|    65536|     0|RECOVERING|    0   |
+--------+-----------+----------+--------+---------+------+----------+--------+

For detailed build progress, see View DDL execution speed and build progress for IMCIs.

What's next

• View DDL execution speed and build progress for IMCIs

• Request distribution overview