tencent cloud

Feedback

Supported Statements and Restrictions

Last updated: 2024-06-07 16:53:57
    This document describes the supported statements of and restrictions on the Column Store Index (CSI) feature.

    Prerequisites

    The kernel version is TDSQL-C for MySQL version 8.0 3.1.14 or later.
    Note:
    For read-only instances that meet the version requirements, the CSI feature can be enabled only on those with four or more CPU cores.

    Supported Statements

    TDSQL-C for MySQL supports queries using SQL statements involving the following scenarios. More scenarios will be gradually supported in the future.
    Scenario
    Description
    Single-table scan
    Forward and reverse scans are supported for full-table scan, index scan, index range scan, and other types of single-table scans.
    Multi-table join
    A variety of join algorithms such as nested loop join, hash join, and sort-merge join are supported for multi-table joins.
    Subquery
    Non-correlated subqueries are supported.
    Data type
    Queries of various types of data are supported, including integer, character, floating-point, and datetime data.
    Operation formula
    Arithmetic expressions (+, -, *, %, /, |, &), conditional expressions (<, <=, >, >=, <>, between/and, In), logical operations (or, and, not), general functions (character, integer, and datetime functions), and aggregate functions (count, sum, avg, min, max) are supported.
    Viewing of execution plan pushed down
    The EXPLAIN statement can be used to view the execution plan that is pushed down. However, the COLUMNSTORE flag is only displayed when using the EXPLAIN FORMAT=TREE statement.

    Restrictions

    TDSQL-C for MySQL will continue to enhance the CSI-based querying capability. Currently, restrictions on the CSI feature are as follows:

    Index Creation

    Only one column store index can be created for each table.
    Column store indexes cannot be created when Generated Column, BLOB, TEXT, JSON, BIT, and GEOMETRY are used.
    The COPY algorithm cannot be used to create or delete a column store index or to delete an index first and then add again.
    Column store indexes cannot be created for non-InnoDB tables.

    DDL-Based Operation on Tables with Column Store Indexes

    Partitioning operations on tables with column store indexes are not supported.

    Use of Column Store Indexes in SELECT Statements

    DML SELECT statements, including INSERT ... SELECT and REPLACE ... SELECT, are not supported.
    Common table expressions (CTEs) are not supported.
    Window functions are not supported.
    WITH ROLLUP is not supported.
    UNION is not supported.
    Transactions with isolation levels other than ReadCommit are not supported.
    Statements for addling locks during data read, such as SELECT FOR UPDATE and SELECT ... FOR SHARE, are not supported.
    Stored Procedures are not supported.
    SQL_MODE statements, such as MODE_PAD_CHAR_TO_FULL_LENGTH and MODE_INVALID_DATES, are not supported.
    GIS-related functions such as SP_WITHIN_FUNC and ST_Distance are not supported.
    Custom functions created by users are not supported.
    XML functions such as XML_STR are not supported.
    Lock-related functions, such as IS_FREE_LOCK, IS_USED_LOCK, RELEASE_LOCK, RELEASE_ALL_LOCKS, and GET_LOCK, are not supported.

    Precautions

    CSI-based queries may have the following issues:
    Unordered Result Set
    When no ORDER is specified, the result set order of CSI-based queries may be different from that of B-tree queries due to the data storage difference. In fact, this is in accordance with ANSI standards. However, it is better to specify a sorting rule to obtain results consistent with those of serial queries if the user is sensitive to the result set order.
    Precision and Truncation Issues
    Use of CSI can lead to subtle differences in the final results, as the storage and computation precision of certain data types may not completely be the same as MySQL, potentially causing precision or truncation issues of the floating-point data.
    Increase in Errors or Alerts
    For queries that report errors or alerts during serial execution, the overall number of errors or alerts may increase when CSI is used for queries because each working thread may report errors or alerts during parallel execution.
    Impact on Resources
    The buffer pool will occupy system memory space when CSI is used.
    The parallel execution of CSI-based queries will consume more threads, memory, and I/O resources, compared with serial queries that use only one thread.
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support