Increased Query execution time

1

Hi Team,

We have recently identified that a query is taking lot of time to execute when fetching data from table stc_b2b_samplett_without_cluster_index which is not having clustered index.
Now we have added clustered indexes, the time taken by the query to be executed is reduced. Please find the DDLS of the tables below and can you please let us know why the time taken to execute a query is reduced after
switching to clustered indexes and does this change has any major impact.

CREATE TABLE stc_b2b_samplett_without_cluster_index (
sr_num varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
status varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
sr_classification varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
type varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
area varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
sub_area varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
created_date varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
operation_time timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
operation_upd_time timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
KEY sr_num () USING CLUSTERED COLUMNSTORE,
UNIQUE KEY primary (sr_num) USING HASH,
SHARD KEY __SHARDKEY (sr_num),
KEY sr_classification (sr_classification) USING HASH,
KEY type (type) USING HASH,
KEY area (area) USING HASH,
KEY sub_area (sub_area) USING HASH,
KEY status (status) USING HASH
) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE=‘STRICT_ALL_TABLES’

CREATE TABLE stc_b2b_samplett_with_cluster_index (
sr_num varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
status varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
sr_classification varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
type varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
area varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
sub_area varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
created_date varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
operation_time timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
operation_upd_time timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
KEY sr_num () USING CLUSTERED COLUMNSTORE,
UNIQUE KEY primary (sr_num) USING HASH,
SHARD KEY __SHARDKEY (sr_num),
KEY clustered (sr_classification,type,area,sub_area,status) USING HASH
) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE=‘STRICT_ALL_TABLES’

Thanks,
Sowmya

2

The important difference is that for your 2nd table, you are using a multi-column hash key.

KEY clustered (sr_classification ,type ,area ,sub_area ,status ) USING HASH

The word “clustered” is not significant in that clause.

This will benefit queries that look up via equality conditions on every element of the key in a single query.

You can check the query plans using EXPLAIN or PROFILE on the original schema and the new one to see if you are benefiting from a seek on this multi-column hash index.

See the documentation on hash indexes for more details.