tencent cloud

Feedback

Columns Syntax

Last updated: 2024-10-09 10:38:07

Feature Overview

The columns syntax is used to query the column information of a table.

Supported Versions

The kernel version should be 3.1.15 or later for TXSQL 8.0.

Applicable Scenarios

It is applicable to scenarios where a lot of column information is queried from a table and the query performance needs improvement.

Use Instructions

List Columns Syntax

A single column is specified:
CREATE TABLE `t1` (
`id` int DEFAULT NULL,
`purchased` varchar(12) DEFAULT NULL,
KEY `idx` (`id`,`purchased`) GLOBAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (`id`)
SUBPARTITION BY LIST COLUMNS (`purchased`)
SUBPARTITION TEMPLATE
(SUBPARTITION s0 VALUES IN ('0', '1', '2') ENGINE = InnoDB,
SUBPARTITION s1 VALUES IN ('5', '6', '8') ENGINE = InnoDB)
(PARTITION p0 VALUES LESS THAN (1990) ,
PARTITION p1 VALUES LESS THAN (1999));
Multiple columns are specified:
CREATE TABLE `t2` (
`id` int DEFAULT NULL,
`purchased` varchar(12) DEFAULT NULL,
KEY `idx` (`id`,`purchased`) GLOBAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (`id`)
SUBPARTITION BY LIST COLUMNS (`purchased`, `id`)
SUBPARTITION TEMPLATE
(SUBPARTITION s0 VALUES IN (('0', 1), ('1', 1), ('2', 1995)) ENGINE = InnoDB,
SUBPARTITION s1 VALUES IN (('5' ,5), ('6', 6)) ENGINE = InnoDB)
(PARTITION p0 VALUES LESS THAN (1990) ,
PARTITION p1 VALUES LESS THAN (1999));

Range Columns Syntax

A single column is specified:
CREATE TABLE `t3` (
`id` int DEFAULT NULL,
`purchased` varchar(12) DEFAULT NULL,
KEY `idx` (`id`,`purchased`) GLOBAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (`id`)
SUBPARTITION BY RANGE COLUMNS (`purchased`)
SUBPARTITION TEMPLATE
(SUBPARTITION s0 VALUES LESS THAN ('5') ENGINE = InnoDB,
SUBPARTITION s1 VALUES LESS THAN ('8') ENGINE = InnoDB)
(PARTITION p0 VALUES LESS THAN (1990) ,
PARTITION p1 VALUES LESS THAN (1999));
Multiple columns are specified:
CREATE TABLE `t4` (
`id` int DEFAULT NULL,
`purchased` varchar(12) DEFAULT NULL,
KEY `idx` (`id`,`purchased`) GLOBAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (`id`)
SUBPARTITION BY RANGE COLUMNS (`purchased`, `id`)
SUBPARTITION TEMPLATE
(SUBPARTITION s0 VALUES LESS THAN ('5', 55) ENGINE = InnoDB,
SUBPARTITION s1 VALUES LESS THAN ('8', 88) ENGINE = InnoDB)
(PARTITION p0 VALUES LESS THAN (1990) ,
PARTITION p1 VALUES LESS THAN (1999));

Supported Data Types of Columns

For supported data types of columns, see official documentation.

Pruning Instructions

Subpartition pruning and partition pruning use the same method and are independent of each other. Specifically, partition pruning can filter the partition key part in a WHERE condition to obtain a partition set s1, and subpartition pruning can filter the subpartition key part in the WHERE condition to obtain a subpartition template set s2. The final result is that s2 in each s1 is selected.

Restrictions

The new parameter txsql_subpartition_support_multiple_columns restricts the use of multiple columns. When it is set to OFF, only single-column syntax is allowed. When it is set to ON, both multiple-column and single-column syntax are allowed.
Parameter Name
Dynamic
Type
Default Value
Valid Values/Value Range
Description
txsql_subpartition_support_multiple_columns
yes
bool
OFF
OFF/ON
Restricts the use of multiple columns. When it is set to OFF, only single-column syntax is allowed. When it is set to ON, both multiple-column and single-column syntax are allowed.
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