Check Columnar Index Coverage for SQL Queries - PolarDB

Before running a query on PolarDB for MySQL, use dbms_imci.check_columnar_index() to verify that every column referenced in the query has a valid In-Memory Column Index (IMCI). The procedure returns the columns that lack IMCI coverage, so you can identify exactly which tables need an IMCI before the query can benefit from columnar engine acceleration.

If any referenced column lacks a valid IMCI, the procedure returns the database name, table name, and column name for each uncovered column.
If all referenced columns have a valid IMCI, the procedure returns an empty result set.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for MySQL cluster running one of the following versions:
- PolarDB for MySQL 8.0.1, revision version 8.0.1.1.30 or later
- PolarDB for MySQL 8.0.2, revision version 8.0.2.2.12 or later
SELECT permission on the tables referenced in the SQL statement

Syntax

dbms_imci.check_columnar_index('<query_string>');

Parameters

Parameter	Description
`query_string`	The SQL statement to parse. Must be a string literal — not a variable or a query result.

query_string must be a valid SELECT statement. INSERT, UPDATE, DELETE, and other DML statements are not supported. If the SELECT statement contains an error (for example, a nonexistent column), the procedure returns an error message instead of a result set.

Usage notes

The stored procedure is case-sensitive.
If the SQL statement includes a fully qualified table name (for example, db_name.table_name), the procedure uses the specified database. If no database is prefixed, run use db_name to switch to the correct database before calling the procedure.
SQL statement length is not explicitly limited, but is constrained by the thread_stack parameter (used during SQL parsing) and max_allowed_packet.
If a column's data type is not supported by IMCI, the procedure returns an error when called.
query_string can be the parameterized form of a prepared statement, but the PREPARE...FROM syntax is not supported.
Escape characters that conflict with the string delimiter per MySQL syntax:
- When using single quotes, replace ' with '':
```
call dbms_imci.check_columnar_index('select t1.a from t1 where t1.b = ''some_string''');
```
- When using double quotes, replace " with "":
```
call dbms_imci.check_columnar_index("select t1.a from t1 where t1.b = ""some_string""");
```
For a complete list of escape rules, see MySQL string literals.

Examples

The following examples use three tables in the test database. t3 has IMCI enabled via comment 'columnar=1'; t1 and t2 do not.

Set up the test tables:

use test;

create table t1 (a int, b int) engine = innodb;
create table t2 (a int, b int) engine = innodb;
create table t3 (a int, b int) engine = innodb comment 'columnar=1';

Check a query that joins tables without IMCI

call dbms_imci.check_columnar_index('select count(t1.a) from t1 inner join t2 on t1.a = t2.a group by t1.b');

Result:

+--------------+------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |
+--------------+------------+-------------+
| test         | t1         | a           |
| test         | t1         | b           |
| test         | t2         | a           |
+--------------+------------+-------------+
3 rows in set (0.01 sec)

Each row in the result identifies a column that lacks a valid IMCI:

TABLE_SCHEMA: the database that contains the column
TABLE_NAME: the table that contains the column
COLUMN_NAME: the column name

The query references t1.a, t1.b, and t2.a, and none of these columns have an IMCI. To enable IMCI acceleration for this query, create an IMCI on t1 and t2, then rerun this check to confirm full coverage.

Check a query against a table with IMCI

call dbms_imci.check_columnar_index('select a, b from t3');

Result:

Empty set (0.00 sec)

All columns referenced in the query (t3.a and t3.b) have a valid IMCI. The query is eligible for columnar engine acceleration.