All Products
Search

This Product

    PolarDB:Automatic IMCI-based query acceleration (AutoIndex)

    Document Center

    PolarDB:Automatic IMCI-based query acceleration (AutoIndex)

    Last Updated:Jun 08, 2026

    PolarDB for MySQL IMCI provides AutoIndex, which automatically accelerates slow SQL queries by creating column indexes.

    Overview

    IMCI of PolarDB for MySQL is designed for complex queries on large datasets in OLAP scenarios. With IMCI, PolarDB for MySQL integrates real-time transaction processing and real-time data analysis as a one-stop HTAP solution.

    image

    Traditionally, accelerating slow queries requires manually adding COLUMNAR=1 to the table COMMENT field via CREATE TABLE or ALTER TABLE. This is inefficient for large numbers of SQL templates with complex logic.

    AutoIndex automates this by creating column indexes on tables referenced by slow queries.

    Technical background

    AutoIndex tunes SQL performance by automatically adding indexes. For row-store indexes, this is well-researched and implemented in Oracle and SQL Server. These systems analyze predicates, ORDER BY, or JOIN operations to identify potential secondary indexes, recompute query cost with the proposed index, and create those that significantly reduce cost.

    For column indexes, AutoIndex is adopted by Redshift, Snowflake, Databricks, and Heatwave. Common techniques include:

    • Automatic loading and unloading: Create column indexes based on query history; drop infrequently used or resource-intensive indexes to save storage.

    • Automatic encoding: Select optimal encoding per column based on data distribution to compress storage.

    • Automatic selection of distribution keys: Balance data in MPP scenarios to reduce skew and optimize shuffling for distributed GROUP BY and JOIN.

    • Automatic selection of sort keys: Accelerate filtering and sorting using common predicates or join/sort columns.

    Creating column indexes in IMCI is less costly and more fault-tolerant than row-store indexes:

    • Lower impact on writes: Row-store indexes degrade write performance on the primary node. IMCI does not materialize column-store data on the primary node — new column indexes sync to the read-only IMCI node through Redo logs with minimal write impact.

    • Higher compression ratio: Column indexes typically achieve 3x–5x compression, making storage overhead of new indexes small.

    • Limited impact on business operations: Lock-free data structures collect SQL trace information, and nonblocking DDL minimizes disruption to business operations.

    How it works

    Slow query tracking and DDL statement generation

    Each cluster node uses SQL Trace to collect slow queries and generate DDL statements for column indexes:

    1. SQL Trace: Uses a lock-free MySQL hash table to track SQL execution by template. Suitable for high-concurrency scenarios with many SQL templates. Performance impact is ≤3% in Sysbench tests.

    2. Trigger condition: When rows scanned by a slow query reach a configured threshold, AutoIndex generates a DDL to add a column index to that table.

    3. Low-overhead design: AutoIndex reuses the cached table list from THD, avoiding repetitive SQL parsing and MDL acquisition with near-zero overhead.

    Query the information_schema.imci_autoindex system table on any node to view tracked slow queries and generated DDL statements:

    SELECT * FROM information_schema.imci_autoindex

    For detailed SQL execution information, query information_schema.sql_sharing:

    SELECT *
FROM information_schema.imci_autoindex autoindex, information_schema.sql_sharing share
WHERE autoindex.sql_id = share.sql_id
  AND share.type = 'SQL'

    Slow query collection, aggregation, and DDL execution

    One read-only IMCI node acts as leader, collecting and aggregating slow query recommendations from all nodes. It sorts recommendations by execution time (descending) and executes DDL statements sequentially until the per-round threshold is met.

    • Instant DDL

      Adding a column index on the primary node is an Instant DDL — it updates only the data dictionary. The Redo log is replicated to the read-only IMCI node, which builds the index in the background.

    • Nonblocking DDL

      Standard DDL blocks new transactions while acquiring an MDL-X lock. Nonblocking DDL lets new transactions proceed if lock acquisition fails, retrying later. AutoIndex DDL is not urgent — failed DDLs retry in subsequent scheduling rounds.

      Query information_schema.imci_autoindex_executed from the cluster endpoint to view recent successful index recommendations and associated slow queries (up to 128 records retained).

    • Scheduling and execution limits

      • By default, the interval between rounds is 1 minute, and a maximum of 5 DDL statements are executed per round.

      • Unexecuted recommendations retry only when the corresponding slow query reoccurs.

      • Resource governance applies during index building — usage rises but node resources are not saturated.

      • The next round starts only after all current-round indexes finish building on the read-only IMCI node.

    Column index activation and usage

    To maximize AutoIndex effectiveness, attach the read-only IMCI node to the cluster endpoint and enable automatic row/column routing. Slow queries trigger column index creation, and once built, the routing mechanism directs qualifying queries to the IMCI node based on cost.

    If your application connects directly to a regular read-only node, manually switch traffic to the read-only IMCI node after the column index is created. Column index status and routing policies are covered in IMCI FAQ.

    Test results

    The following example uses the TPC-H 100 GB test suite.

    Note

    The TPC-H implementation described in this topic is based on the TPC-H benchmark but does not comply with all TPC-H requirements. The test results in this topic are not comparable to published TPC-H benchmark results.

    • Environment setup: Run all 22 TPC-H queries every minute. Enable HTAP Optimization (Transactional/Analytical Processing Splitting) on the cluster endpoint. Set row-store parallel degree to 8 (to shorten test duration).

    • Test results: Query performance improved significantly in subsequent rounds. By the end of round 1 (around the 12th query), AutoIndex had created column indexes for most tables, and the optimizer routed these queries to the read-only IMCI node.

    Execution round

    Total time (s)

    Row/column plans

    Performance gain

    1

    8,293

    10 / 12

    -

    2

    118

    1 / 21

    Approx. 70x

    3

    110

    0 / 22

    Approx. 75x

    Benefits

    IMCI delivers benefits in compatibility, performance, and cost:

    • Fully MySQL compatible: Supports all MySQL data types and the MySQL protocol.

    • Excellent HTAP performance: Delivers 10x–100x performance gains for analytical workloads.

    • Cost-effective hybrid storage: Hybrid storage ensures transactional consistency; column indexes deliver superior performance at lower cost for analytical queries.

    • One-click automatic acceleration: One-click enablement, no complex configuration or tuning required.

    • Reduced operational overhead: Automatically creates column indexes for slow queries, eliminating manual optimization.

    • Lower IT costs: Creates column indexes only for slow queries — not all tables — saving memory and storage.

    Supported versions

    • Enterprise Edition clusters must meet the following conditions:

      • Series: Cluster Edition.

      • Database engine version:

        • MySQL 8.0.1, with a revision of 8.0.1.1.45.2 or later.

        • MySQL 8.0.2, with a revision of 8.0.2.2.27 or later.

    • Standard Edition clusters must meet the following conditions:

      • CPU architecture: X86.

      • Database engine version: MySQL 8.0.1, with a revision of 8.0.1.1.45.2 or later.

    Note

    To check your cluster version, see Query the engine version.

    Usage notes

    • Multi-master Cluster (Limitless) Edition and Serverless clusters do not support AutoIndex.

    • After you enable AutoIndex:

      • Read-only IMCI nodes follow the same billing rules as regular compute nodes.

      • The system creates column indexes based on slow SQL queries. This data consumes storage space.

      • The SQL Trace feature records slow SQL execution history, with a typical performance overhead of ≤3% per node.

    • AutoIndex uses nonblocking DDL to add column indexes, ensuring new transactions can still access the target table even if an MDL-X lock cannot be obtained.

    Enable AutoIndex

    1. Log on to the PolarDB console. In the left navigation bar, click Clusters, select the Region where the cluster is located, and click the ID of the target cluster to go to the cluster details page.

    2. On the Basic Information page, find the Automatic IMCI-based Query Acceleration field and click Enable.

    3. The next step depends on whether your cluster has a read-only IMCI node:

      • If your cluster has a read-only IMCI node, click OK in the Enable Automatic IMCI-based Query Acceleration dialog box to enable the feature.

      • If your cluster does not have a read-only IMCI node, click OK in the Enable Automatic IMCI-based Query Acceleration dialog box. You are redirected to the page for adding a read-only IMCI node.

        Note

        • You can add a read-only IMCI node immediately after you click OK, or you can manually add a read-only IMCI node later.

        • After you enable this feature, your cluster must contain at least one read-only IMCI node. Otherwise, even if the status of Automatic IMCI-based Query Acceleration is Enable, query acceleration will not be active.

        • Without a read-only IMCI node, the system records slow SQL execution history via SQL Trace but does not create column indexes — no acceleration occurs.

    Disable AutoIndex

    1. Log on to the PolarDB console, click Clusters in the left navigation bar, select the Region where the cluster is located, and click the target cluster ID to go to the cluster details page.

    2. On the Basic Information page, find the Automatic IMCI-based Query Acceleration field and click Close.

    3. In the Disable Automatic IMCI-based Query Acceleration dialog box, click OK to disable the feature.

    Note

    Disabling AutoIndex deactivates the feature but does not remove existing read-only IMCI nodes or their data. To remove them, delete the read-only IMCI nodes in the console or remove the column indexes by using DDL statements.