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