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.
Was this page helpful?