Optimize IMCI Sort Keys for Faster Analytics on PolarDB - PolarDB - Alibaba Cloud - PolarDB

This topic simulates a production environment and demonstrates how to use In-Memory Column Index (IMCI) to improve query performance for large-scale datasets, addressing slow queries on single and multiple tables.

What is In-Memory Column Index (IMCI)

An In-Memory Column Index (IMCI) stores all or part of a table's columns in a columnar format on a PolarDB for MySQL read-only node, creating a hybrid row-column storage model. The query optimizer is also enhanced with new execution operators designed for columnar storage. This significantly improves the performance of data analytics and complex queries on large datasets. For more information, see What is In-Memory Column Index (IMCI)?.

Procedure

Prerequisites

Cluster
- Product version: Enterprise Edition.
- Series: Cluster Edition (Dedicated).
- Kernel version: 8.0.1.1.45.2.
- Hot standby cluster: Enabled.
- Compute nodes: 32 cores 256 GB (polar.mysql.x8.4xlarge), one primary node and one read-only node (hot standby).
- Storage type: PSL5.
- Parameter template: MySQL_InnoDB_8.0_Standard Edition_Default parameter template.

Data

A 100 GB dataset based on the TPC-H benchmark.

-- Query the row count and size of tables in the database.
+----------+----------+-----------+-----------------+
| Database | Table    | Rows      | Total Size (GB) |
+----------+----------+-----------+-----------------+
| tpch     | customer |  13179406 |            2.59 |
| tpch     | lineitem | 590446240 |           87.52 |
| tpch     | nation   |        25 |            0.00 |
| tpch     | orders   | 142929780 |           18.70 |
| tpch     | part     |  19354445 |            3.11 |
| tpch     | partsupp |  67862725 |           20.45 |
| tpch     | region   |         5 |            0.00 |
| tpch     | supplier |    986923 |            0.17 |
+----------+----------+-----------+-----------------+

Note

The row count and table size are affected by various factors, such as indexes, storage engines, statistics, and system tables. Your actual output may differ from the results shown.
The TPC-H workload in this topic is based on the TPC-H benchmark but does not comply with all its requirements. Therefore, the test results in this topic are not comparable to published TPC-H benchmark results.

Configure IMCI

Add a read-only node for IMCI. In this topic, the added node has the same specifications as the primary node: 32 cores and 256 GB of memory (polar.mysql.x8.4xlarge). For more information, see Add a read-only node for IMCI.

Single-table queries

Simulate a scenario with a slow-running SQL query. Run the following single-table queries before creating an IMCI and record their execution times.

Single-table scan and filter

SELECT * FROM lineitem WHERE L_COMMENT > 'aaaaaaaa' AND L_COMMENT < 'aaaaaaz';

-- Execution result
Empty set (8 min 47.29 sec)

Single-column aggregation (AGG)

SELECT SUM(L_DISCOUNT) from lineitem;

-- Execution result
+-----------------+
| SUM(L_DISCOUNT) |
+-----------------+
|     30001636.44 |
+-----------------+
1 row in set (2 min 6.64 sec)

Grouped aggregation (GROUP BY)

SELECT AVG(L_DISCOUNT) FROM lineitem WHERE L_SHIPDATE <= date '1998-12-01' - interval '90' day GROUP BY L_RETURNFLAG, L_LINESTATUS;

-- Execution result
+-----------------+
| AVG(L_DISCOUNT) |
+-----------------+
|        0.049998 |
|        0.050001 |
|        0.050002 |
|        0.049985 |
+-----------------+
4 rows in set (6 min 28.96 sec)

Deep pagination (ORDER BY+LIMIT)

SELECT L_ORDERKEY, SUM(L_QUANTITY) FROM lineitem GROUP BY L_ORDERKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 1000000, 100;

-- Execution result
+------------+-----------------+
| L_ORDERKEY | SUM(L_QUANTITY) |
+------------+-----------------+
|   25226310 |          244.00 |
|     ...    |            ...  |
|  494738146 |          244.00 |
+------------+-----------------+
100 rows in set (12 min 24.22 sec)

Create an IMCI. For more information, see Create an In-Memory Column Index.

ALTER TABLE lineitem COMMENT 'COLUMNAR=1 lineitem table comment';

-- Execution result
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Monitor the IMCI build progress and wait for it to complete. For more information, see Check the build progress of an IMCI.

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

-- The following result shows an IMCI build in progress.
+-------------+------------+---------------------+---------------------+-------------+----------+------------------+--------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| SCHEMA_NAME | TABLE_NAME | CREATED_AT          | STARTED_AT          | FINISHED_AT | STATUS   | APPROXIMATE_ROWS | SCANNED_ROWS | SCAN_SECOND | SORT_ROUNDS | SORT_SECOND | BUILD_ROWS | BUILD_SECOND | AVG_SPEED | SPEED_LAST_SECOND | ESTIMATE_SECOND |
+-------------+------------+---------------------+---------------------+-------------+----------+------------------+--------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| tpch        | lineitem   | 2024-10-21 13:44:02 | 2024-10-21 13:44:02 |             | Building | 590446240        | 36718757(6%) | 19          | 0           | 0           | 0(0%)      | 0            | 1848522   | 0                 | 299             |
+-------------+------------+---------------------+---------------------+-------------+----------+------------------+--------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

-- The following result shows a completed IMCI build.
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| SCHEMA_NAME | TABLE_NAME | CREATED_AT          | STARTED_AT          | FINISHED_AT         | STATUS       | APPROXIMATE_ROWS | SCANNED_ROWS    | SCAN_SECOND | SORT_ROUNDS | SORT_SECOND | BUILD_ROWS | BUILD_SECOND | AVG_SPEED | SPEED_LAST_SECOND | ESTIMATE_SECOND |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| tpch        | lineitem   | 2024-10-21 13:44:02 | 2024-10-21 13:44:02 | 2024-10-21 13:50:11 | Safe to read | 590446240        | 600037902(100%) | 369         | 0           | 0           | 0(0%)      | 0            | 1625058   | 0                 | 0               |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

After creating the IMCI for the lineitem table, run the single-table queries again and record the execution times.

Single-table scan and filter

SELECT * FROM lineitem WHERE L_COMMENT > 'aaaaaaaa' AND L_COMMENT < 'aaaaaaz';

-- Execution result
Empty set (1.47 sec)

Single-column aggregation (AGG)

SELECT SUM(L_DISCOUNT) from lineitem;

-- Execution result
+-----------------+
| SUM(L_DISCOUNT) |
+-----------------+
|     30001636.44 |
+-----------------+
1 row in set (0.06 sec)

Grouped aggregation (GROUP BY)

SELECT AVG(L_DISCOUNT) FROM lineitem WHERE L_SHIPDATE <= date '1998-12-01' - interval '90' day GROUP BY L_RETURNFLAG, L_LINESTATUS;

-- Execution result
+-----------------+
| AVG(L_DISCOUNT) |
+-----------------+
|        0.050001 |
|        0.050002 |
|        0.049985 |
|        0.049998 |
+-----------------+
4 rows in set (2.54 sec)

Deep pagination (ORDER BY+LIMIT)

SELECT L_ORDERKEY, SUM(L_QUANTITY) FROM lineitem GROUP BY L_ORDERKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 1000000, 100;

-- Execution result
+------------+-----------------+
| L_ORDERKEY | SUM(L_QUANTITY) |
+------------+-----------------+
|  299074498 |          244.00 |
|     ...    |            ...  |
|  168679332 |          244.00 |
+------------+-----------------+
100 rows in set (12.80 sec)

Execution time comparison (in seconds).

Query type	PolarDB (IMCI)	PolarDB (Row store)
Single-table scan and filter	1.47	527.29
Single-column aggregation (AGG)	0.06	126.64
Grouped aggregation (GROUP BY)	2.54	388.96
Deep pagination performance (ORDER BY+LIMIT)	12.80	744.22

Adding an IMCI significantly improves the performance of single-table SQL queries.

Note

This data is a benchmark for evaluating SQL execution performance, not an absolute standard. Actual SQL execution times depend on multiple dynamic factors, including cluster configuration, current connection count, concurrent query volume, and real-time system load.

Multi-table queries and subqueries

In this section, we simulate a slow query scenario where an IMCI does not cover all required tables and columns. Run the following SQL queries and record their execution times.

Note

In the following SQL queries, an IMCI is created only for the lineitem table. Other tables do not have an IMCI.
If a query's required tables or columns are not fully covered by an IMCI, acceleration does not apply.
If you are unsure whether the tables or columns required by a query are fully covered, you can use the dbms_imci.check_columnar_index('<query_string>'); stored procedure to check. To help you create IMCIs quickly, PolarDB provides stored procedures to get the DDL statements for creating IMCIs. For more information, see DDL helper tools for IMCIs.

Multi-table join (JOIN)

SELECT
  COUNT(l3.L_DISCOUNT)
FROM
  (
    (
      (
        (
          (
            nation n1 STRAIGHT_JOIN nation n2 on n1.N_NATIONKEY = n2.N_NATIONKEY
          )
          STRAIGHT_JOIN supplier on n2.N_NATIONKEY = supplier.S_NATIONKEY and S_SUPPKEY < 2000
        )
        STRAIGHT_JOIN lineitem AS l1 on l1.L_SUPPKEY = supplier.S_SUPPKEY
      )
      STRAIGHT_JOIN lineitem AS l2 on l1.L_ORDERKEY = l2.L_ORDERKEY and l1.L_LINENUMBER = l2.L_LINENUMBER
    )
    STRAIGHT_JOIN lineitem AS l3 on l2.L_ORDERKEY = l3.L_ORDERKEY and l2.L_LINENUMBER = l3.L_LINENUMBER
  )
GROUP BY 
  n1.N_NAME;

The query timed out and was terminated. The query timeout period is 7,200 seconds. Therefore, the execution time is recorded as more than 7,200 seconds.

Correlated subquery

SELECT 
  O_ORDERPRIORITY, COUNT(*) as ORDER_COUNT 
FROM
  orders
WHERE
  O_ORDERDATE >= '1995-01-01' AND
  O_ORDERDATE < date_add('1995-01-01', interval '3' month) AND
  EXISTS 
    (
      SELECT * FROM lineitem WHERE L_ORDERKEY = O_ORDERKEY AND L_COMMITDATE < L_RECEIPTDATE
    )
GROUP BY 
  O_ORDERPRIORITY
ORDER BY
  O_ORDERPRIORITY;

-- Execution result
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     1028353 |
| 2-HIGH          |     1030059 |
| 3-MEDIUM        |     1028615 |
| 4-NOT SPECIFIED |     1028496 |
| 5-LOW           |     1029615 |
+-----------------+-------------+
5 rows in set (4 min 9.51 sec)

Multi-table join with subquery

SELECT 
  C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, SUM(L_QUANTITY)
FROM 
  (
    SELECT * FROM orders WHERE O_ORDERKEY IN 
      (
        SELECT L_ORDERKEY FROM lineitem GROUP BY L_ORDERKEY HAVING SUM(L_QUANTITY) > 300
      ) 
  ) AS tmp, customer, lineitem 
WHERE 
  C_CUSTKEY = O_CUSTKEY AND 
  O_ORDERKEY = L_ORDERKEY
GROUP BY 
  C_NAME, 
  C_CUSTKEY, 
  O_ORDERKEY, 
  O_ORDERDATE, 
  O_TOTALPRICE
ORDER BY 
  O_TOTALPRICE DESC, 
  O_ORDERDATE;

+--------------------+-----------+------------+-------------+--------------+-----------------+
| C_NAME             | C_CUSTKEY | O_ORDERKEY | O_ORDERDATE | O_TOTALPRICE | SUM(L_QUANTITY) |
+--------------------+-----------+------------+-------------+--------------+-----------------+
| Customer#011472112 |  11472112 |  458304292 | 1998-02-05  |    591036.15 |          322.00 |
|        ...         |     ...   |     ...    |     ...     |       ...    |            ...  |
| Customer#003777694 |   3777694 |  470363105 | 1997-04-06  |    349914.00 |          302.00 |
| Customer#009446411 |   9446411 |  592379937 | 1995-12-29  |    343496.05 |          304.00 |
+--------------------+-----------+------------+-------------+--------------+-----------------+
6398 rows in set (12 min 46.15 sec)

Batch-create IMCIs for the tpch database. For more information, see Batch-create IMCIs.

CREATE COLUMNAR INDEX FOR TABLES IN tpch;

-- Execution result
+------------+-------------------+
| Table_Name | Result            |
+------------+-------------------+
| customer   | Ok                |
| lineitem   | Skip by no change |
| nation     | Ok                |
| orders     | Ok                |
| part       | Ok                |
| partsupp   | Ok                |
| region     | Ok                |
| supplier   | Ok                |
+------------+-------------------+
8 rows in set (56.74 sec)

Monitor the IMCI build progress and wait for them to complete. For more information, see Check the build progress of an IMCI.

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

-- The following result shows that the IMCI builds for all tables are complete.
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| SCHEMA_NAME | TABLE_NAME | CREATED_AT          | STARTED_AT          | FINISHED_AT         | STATUS       | APPROXIMATE_ROWS | SCANNED_ROWS    | SCAN_SECOND | SORT_ROUNDS | SORT_SECOND | BUILD_ROWS | BUILD_SECOND | AVG_SPEED | SPEED_LAST_SECOND | ESTIMATE_SECOND |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| tpch        | region     | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | Safe to read | 5                | 5(100%)         | 0           | 0           | 0           | 0(0%)      | 0            | 150       | 0                 | 0               |
| tpch        | lineitem   | 2024-10-21 14:36:13 | 2024-10-21 14:36:13 | 2024-10-21 14:42:23 | Safe to read | 590446240        | 600037902(100%) | 370         | 0           | 0           | 0(0%)      | 0            | 1620776   | 0                 | 0               |
| tpch        | supplier   | 2024-10-21 14:44:16 | 2024-10-21 14:44:16 | 2024-10-21 14:44:17 | Safe to read | 986923           | 1000000(100%)   | 1           | 0           | 0           | 0(0%)      | 0            | 784971    | 0                 | 0               |
| tpch        | part       | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:43:38 | Safe to read | 19354445         | 20000000(100%)  | 11          | 0           | 0           | 0(0%)      | 0            | 1784854   | 0                 | 0               |
| tpch        | customer   | 2024-10-21 14:43:19 | 2024-10-21 14:43:19 | 2024-10-21 14:43:27 | Safe to read | 13179406         | 15000000(100%)  | 7           | 0           | 0           | 0(0%)      | 0            | 2051651   | 0                 | 0               |
| tpch        | nation     | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | Safe to read | 25               | 25(100%)        | 0           | 0           | 0           | 0(0%)      | 0            | 739       | 0                 | 0               |
| tpch        | partsupp   | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:44:16 | Safe to read | 67862725         | 80000000(100%)  | 49          | 0           | 0           | 0(0%)      | 0            | 1620131   | 0                 | 0               |
| tpch        | orders     | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:44:27 | Safe to read | 142929780        | 150000000(100%) | 59          | 0           | 0           | 0(0%)      | 0            | 2501701   | 0                 | 0               |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
8 rows in set, 1 warning (0.00 sec)

After batch-creating IMCIs for the tpch database, run the multi-table queries and subqueries again and record the execution times.

Multi-table join (JOIN)

SELECT
  COUNT(l3.L_DISCOUNT)
FROM
  (
    (
      (
        (
          (
            nation n1 STRAIGHT_JOIN nation n2 on n1.N_NATIONKEY = n2.N_NATIONKEY
          )
          STRAIGHT_JOIN supplier on n2.N_NATIONKEY = supplier.S_NATIONKEY and S_SUPPKEY < 2000
        )
        STRAIGHT_JOIN lineitem AS l1 on l1.L_SUPPKEY = supplier.S_SUPPKEY
      )
      STRAIGHT_JOIN lineitem AS l2 on l1.L_ORDERKEY = l2.L_ORDERKEY and l1.L_LINENUMBER = l2.L_LINENUMBER
    )
    STRAIGHT_JOIN lineitem AS l3 on l2.L_ORDERKEY = l3.L_ORDERKEY and l2.L_LINENUMBER = l3.L_LINENUMBER
  )
GROUP BY 
  n1.N_NAME;

+----------------------+
| COUNT(l3.L_DISCOUNT) |
+----------------------+
|                56930 |
|                 ...  |
|                49995 |
+----------------------+
25 rows in set (6.25 sec)

Correlated subquery

SELECT 
  O_ORDERPRIORITY, COUNT(*) as ORDER_COUNT 
FROM
  orders
WHERE
  O_ORDERDATE >= '1995-01-01' AND
  O_ORDERDATE < date_add('1995-01-01', interval '3' month) AND
  EXISTS 
    (
      SELECT * FROM lineitem WHERE L_ORDERKEY = O_ORDERKEY AND L_COMMITDATE < L_RECEIPTDATE
    )
GROUP BY 
  O_ORDERPRIORITY
ORDER BY
  O_ORDERPRIORITY;

-- Execution result
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     1028353 |
| 2-HIGH          |     1030059 |
| 3-MEDIUM        |     1028615 |
| 4-NOT SPECIFIED |     1028496 |
| 5-LOW           |     1029615 |
+-----------------+-------------+
5 rows in set (2.49 sec)

Multi-table join with subquery

SELECT 
  C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, SUM(L_QUANTITY)
FROM 
  (
    SELECT * FROM orders WHERE O_ORDERKEY IN 
      (
        SELECT L_ORDERKEY FROM lineitem GROUP BY L_ORDERKEY HAVING SUM(L_QUANTITY) > 300
      ) 
  ) AS tmp, customer, lineitem 
WHERE 
  C_CUSTKEY = O_CUSTKEY AND 
  O_ORDERKEY = L_ORDERKEY
GROUP BY 
  C_NAME, 
  C_CUSTKEY, 
  O_ORDERKEY, 
  O_ORDERDATE, 
  O_TOTALPRICE
ORDER BY 
  O_TOTALPRICE DESC, 
  O_ORDERDATE;

-- Execution result
+--------------------+-----------+------------+-------------+--------------+-----------------+
| C_NAME             | C_CUSTKEY | O_ORDERKEY | O_ORDERDATE | O_TOTALPRICE | SUM(L_QUANTITY) |
+--------------------+-----------+------------+-------------+--------------+-----------------+
| Customer#011472112 |  11472112 |  458304292 | 1998-02-05  |    591036.15 |          322.00 |
|        ...         |     ...   |     ...    |     ...     |       ...    |            ...  |
| Customer#003777694 |   3777694 |  470363105 | 1997-04-06  |    349914.00 |          302.00 |
| Customer#009446411 |   9446411 |  592379937 | 1995-12-29  |    343496.05 |          304.00 |
+--------------------+-----------+------------+-------------+--------------+-----------------+
6398 rows in set (16.16 sec)

Execution time comparison (in seconds).
Query type
PolarDB (IMCI)
PolarDB (Row store)
Multi-table join (JOIN) performance
6.25
>7200
Correlated subquery performance
2.49
249.51
Multi-table join with subquery performance
16.16
766.15
Adding IMCIs significantly improves the performance of multi-table queries and subqueries.
Note
This data is a benchmark for evaluating SQL execution performance, not an absolute standard. Actual SQL execution times depend on multiple dynamic factors, including cluster configuration, current connection count, concurrent query volume, and real-time system load.

HTAP request routing

After you add a read-only IMCI node, the Cluster Endpoint is configured for Cluster Endpoint by default. This configuration is suitable for scenarios where both online analytical processing (OLAP) and online transaction processing (OLTP) requests access the database through the same application. Read requests are automatically routed to either the IMCI node or a row-store node based on the number of rows scanned. If your OLAP and OLTP workloads originate from different applications, you can configure manual routing. This involves creating separate endpoints for each application and assigning the row-store and IMCI nodes to the Node Settings of the corresponding endpoint. This ensures effective workload separation between the row store and column store. For more information, see HTAP-based request distribution among row-store and IMCI nodes.

The following diagrams illustrate automatic and manual request routing:

Advanced usage

For more information, see Advanced IMCI usage.

Sort key

For more information, see Set sort keys for IMCIs.

IMCI data is organized in row groups, and each row group contains 64,000 rows by default. Within each row group, different columns are packed into separate column data blocks. These blocks are built in parallel based on the primary key order of the original row store data, resulting in an unordered state. Setting a sort key reorders the column data blocks to improve query performance.

Enable the IMCI sorting feature by setting the imci_enable_pack_order_key parameter to ON. This enables data sorting when a new IMCI is created.
Note
- The default value of the imci_enable_pack_order_key parameter is ON. If you have not modified this parameter, you can skip this step.
- In the PolarDB console, cluster parameters are prefixed with loose_ for compatibility with MySQL configuration files. If you need to modify the imci_enable_pack_order_key parameter in the PolarDB console, select the parameter with the loose_ prefix (loose_imci_enable_pack_order_key). For more information, see Set cluster and node parameters.

Before adding a sort key, run the following SQL query and record its execution time.

SELECT
  L_SHIPMODE,
  SUM(CASE
      WHEN O_ORDERPRIORITY = '1-URGENT' OR O_ORDERPRIORITY = '2-HIGH'
      THEN 1
      ELSE 0
      END) AS high_line_count,
  SUM(CASE
      WHEN O_ORDERPRIORITY <> '1-URGENT' AND O_ORDERPRIORITY <> '2-HIGH'
      THEN 1
      ELSE 0
      END) AS low_line_count
FROM
    orders,
    lineitem
WHERE
    O_ORDERKEY = L_ORDERKEY
    AND L_SHIPMODE in ('MAIL', 'SHIP')
    AND L_COMMITDATE <  L_RECEIPTDATE
    AND L_SHIPDATE < L_COMMITDATE
    AND L_RECEIPTDATE >= date '1994-01-01'
    AND L_RECEIPTDATE < date '1994-01-01' + interval '1' year
GROUP BY
    L_SHIPMODE
ORDER BY
    L_SHIPMODE;

-- Execution result
+------------+-----------------+----------------+
| L_SHIPMODE | high_line_count | low_line_count |
+------------+-----------------+----------------+
| MAIL       |          623115 |         934713 |
| SHIP       |          622979 |         934534 |
+------------+-----------------+----------------+
2 rows in set (4.35 sec)

Add the order_key attribute to the lineitem table to build sorted IMCI data.

ALTER TABLE lineitem COMMENT='COLUMNAR=1 order_key=L_RECEIPTDATE,L_SHIPMODE lineitem table comment';

Wait for the sorted IMCI data to finish building. For more information, see Build sorted IMCI data and compare query times.

Run the SQL query from Step 2 again and record the execution time.

-- Execution result
+------------+-----------------+----------------+
| L_SHIPMODE | high_line_count | low_line_count |
+------------+-----------------+----------------+
| MAIL       |          623115 |         934713 |
| SHIP       |          622979 |         934534 |
+------------+-----------------+----------------+
2 rows in set (0.88 sec)

Execution time comparison (in seconds).
Ordered dataset
Unordered dataset
0.88
4.35
Note
This data is a benchmark for evaluating SQL execution performance, not an absolute standard. Actual SQL execution times depend on multiple dynamic factors, including cluster configuration, current connection count, concurrent query volume, and real-time system load.

IMCI serverless

For more information, see Enable serverless for a read-only IMCI node.

The serverless feature of the cloud-native database PolarDB provides dynamic, elastic scaling capabilities. Nodes in a cluster can scale elastically within seconds to handle sudden workload spikes without disrupting your business operations. During periods of low workload, this mechanism automatically scales down resources to reduce costs. For more information about the serverless feature, see Serverless.

If your business experiences significant fluctuations in workload, or if you are concerned that your current cluster configuration cannot handle sudden workload spikes, you can Enable Serverless in the Basic Information > Database Nodes section of your cluster. For more information, see Enable the serverless feature for a fixed-specification cluster.

More information

Billing

The IMCI feature is free to use. You are charged only for the read-only IMCI nodes, which are billed as standard compute nodes. For more information, see Billing of compute nodes. IMCI also consumes storage space. For more information, see Billing of storage space.

Note

Compared to a row store, IMCI typically achieves a compression ratio between 3:1 and 10:1, occupying about 10% to 30% of the storage space of the equivalent row store. This results in an additional 10% to 30% increase in data storage usage.

Performance

Query performance
- IMCI significantly accelerates most complex queries, with performance sometimes improving by up to 100x.
- Compared with the traditional OLAP database ClickHouse, the performance of a PolarDB for MySQL cluster with IMCI enabled is comparable, with advantages in certain scenarios. IMCI excels in scenarios such as single-table scans, aggregations (AGG), and joins. Future IMCI versions will continue to be optimized for aggregation acceleration, window functions, and other features.
Note
For more information, see Performance improvements.
Write performance
The impact of adding an IMCI on write performance is generally within 5%. When testing the oltp_insert workload with the Sysbench test suite, write performance decreases by about 3% after an IMCI is added.

Get expert support

If you have any questions about IMCI, you can join our DingTalk group by searching for the group ID 27520023189. You can ask an expert in the group directly by using the @ mention feature.

FAQ

Query not using IMCI

After you add a read-only IMCI node, a query uses the IMCI for acceleration only when the following conditions are met: IMCIs must be created for all tables in the query, the query's estimated execution cost must exceed a specific threshold, and the query must be routed to the read-only IMCI node. If a query does not use the IMCI, troubleshoot the issue by following these steps:

Confirm that the SQL query is routed to the read-only IMCI node.
- Check whether the Node Settings of the endpoint include the read-only IMCI node.
- Use the SQL Explorer feature to confirm if the query was routed to the read-only IMCI node.
If you use a Cluster Endpoint with Transactional/Analytical Processing Splitting enabled, the database proxy routes the query to the read-only IMCI node if its estimated execution cost exceeds the threshold set by loose_imci_ap_threshold or loose_cost_threshold_for_imci. You can also add the /*FORCE_IMCI_NODES*/ hint before the SELECT keyword to force the query to the read-only IMCI node. For more information, see Configure the threshold for automatic request routing. Example:
For kernel versions 8.0.1.1.39 and 8.0.2.2.23 or later, the loose_imci_ap_threshold parameter is deprecated. Use the loose_cost_threshold_for_imci parameter instead.
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;
```
Creating a new endpoint can ensure that SQL queries are always routed to the read-only IMCI node for execution. For more information, see Create a custom endpoint.
Confirm that the query's estimated execution cost exceeds the configured threshold.
On a read-only IMCI node, the optimizer estimates the cost of a query. If the estimated execution cost is higher than the threshold set by loose_imci_ap_threshold or loose_cost_threshold_for_imci, the query uses the IMCI. Otherwise, it uses the original row-based index.
After you confirm that the SQL query is forwarded to an In-Memory Column Index (IMCI) read-only node, if you use EXPLAIN to view the execution plan and find that it still does not use an IMCI, you can compare the estimated execution cost with the preset threshold to determine whether the IMCI is not used because the estimated execution cost is too low. You can obtain the estimated execution cost of the last SQL query by querying the Last_query_cost_for_imci variable:
```
-- Use EXPLAIN to view the execution plan of the SQL query.
EXPLAIN SELECT * FROM t1;
-- Get the estimated execution cost of the last query.
SHOW STATUS LIKE 'Last_query_cost_for_imci';
```
If you use a cluster endpoint to connect to the database, we recommend that you add the HINT syntax /*ROUTE_TO_LAST_USED*/ before SHOW STATUS LIKE 'Last_query_cost_for_imci' to ensure that you can query the estimated execution cost of the previous statement on the correct node. For example, /*ROUTE_TO_LAST_USED*/SHOW STATUS LIKE 'Last_query_cost_for_imci';
If the query's estimated execution cost is below the threshold, consider adjusting the value of loose_imci_ap_threshold or loose_cost_threshold_for_imci. For example, you can use a hint to adjust the threshold for a single query:
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;
```
Confirm that the tables and columns in the query are fully covered by an IMCI.
You can use the built-in stored procedure dbms_imci.check_columnar_index('<query_string>') to check IMCI coverage for the tables and columns in a query. For more information, see Check whether an IMCI is created for tables and columns in a query. Example:
```
CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');
```
If a query is not fully covered, the procedure returns the uncovered tables and columns. You must then create an IMCI for each of them. If the query is fully covered, the procedure returns an empty result set.
Check for unsupported SQL features.
Review the IMCI syntax and limitations to confirm if a specific SQL feature is supported by IMCI. For more information, see IMCI syntax and limitations.

If the SQL query still does not use the IMCI after you follow these steps, get expert support or contact us.

Creating the right IMCI

Add an In-Memory Column Index (IMCI) for the required columns in your SQL statements. For more information, see Check whether an IMCI is created for a table or column in an SQL statement.

An SQL statement can use an IMCI for queries only when the IMCI fully covers all required columns. If the required columns in an SQL statement are not fully covered, use the ALTER TABLE statement to add an IMCI. PolarDB provides a series of built-in stored procedures to help with this operation.

Note

Use the dbms_imci.columnar_advise() stored procedure to get the Data Definition Language (DDL) statement needed to create an IMCI for a specific SQL statement. If you create the IMCI using this DDL statement, the SQL statement is guaranteed to be fully covered by the IMCI. For more information, see Obtain the DDL statement for creating an IMCI.
```
dbms_imci.columnar_advise('<query_string>');
```
Use the dbms_imci.columnar_advise_begin(), dbms_imci.columnar_advise_end(), and dbms_imci.columnar_advise() stored procedures to get the DDL statements needed to create IMCIs for a batch of SQL statements. For more information, see Obtain DDL statements for creating IMCIs in batches.

Ordered dataset	Unordered dataset
0.88	4.35