Create multi column hash index in columnstore table

Help
1

An error occurred when I was creating Hash index in the column store table.

CREATE DATABASE `test`;

CREATE TABLE `test`.`tb_emp_modi_log1`(
`serialNo` INT AUTO_INCREMENT, 
`empCode` VARCHAR(10) NOT NULL, 
`startDate` VARCHAR(10) NOT NULL, 
`endDate` VARCHAR(10) NOT NULL,
`posiCode` VARCHAR(5) NULL,
`regEmpCode` VARCHAR(10) NOT NULL DEFAULT 'ADMIN', 
`regDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(), 
`modiEmpCode` VARCHAR(10) NOT NULL DEFAULT 'ADMIN', 
`modiDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(), 
SHARD KEY (`empCode`), 
PRIMARY KEY (`serialNo` ASC, `empCode` ASC),
KEY `idx_emp_modi_log_start_end_INC`(`startDate` ASC, `endDate` ASC, `empCode`, `posiCode`) USING HASH,
KEY(`regDate`) USING CLUSTERED COLUMNSTORE );


CREATE TABLE `test`.`tb_emp_modi_log2`(
`serialNo` INT AUTO_INCREMENT, 
`empCode` VARCHAR(10) NOT NULL, 
`startDate` VARCHAR(10) NOT NULL, 
`endDate` VARCHAR(10) NOT NULL,
`posiCode` VARCHAR(5) NULL,
`regEmpCode` VARCHAR(10) NOT NULL DEFAULT 'ADMIN', 
`regDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(), 
`modiEmpCode` VARCHAR(10) NOT NULL DEFAULT 'ADMIN', 
`modiDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(), 
SHARD KEY (`empCode`), 
PRIMARY KEY (`serialNo` ASC, `empCode` ASC),
KEY(`regDate`) USING CLUSTERED COLUMNSTORE );

CREATE INDEX `idx_emp_modi_log_start_end_INC` ON `test`.`tb_emp_modi_log2` (`startDate` ASC, `endDate` ASC, `empCode`, `posiCode`) USING HASH;

When an index is specified in [CREATE TABLE] DDL like tb_emp_modi_log1, the index was generated normally.
However, if I add Hash Index after creating a table like tb_emp_modi_log2, it wasn’t generated and the following error message was output.

SQL Error [2560] [HY000]: (conn=8999) Feature 'Adding an INDEX with multiple columns on a columnstore table where any column in the new index is already in an index' is not supported by SingleStore. As a workaround, please drop single column keys from each column first.

Did I write the wrong query(CREATE INDEX)?
Please check it out.

2

Dear @minkyung.kang,

Thanks for the feedback on this. Right now, if people already have a single/multicolumn primary/unique key, then there is no way to add another multicolumn index which covers any column from the primary key. Our engineering team will investigate making this work. There’s no timetable for making it work yet.

You may be able to get some benefit by creating multiple single-column hash keys, one for each column of the multi-column key you wanted, because we can do index intersection to do the lookup. But if any of the columns are very low-cardinality, that probably is not a good idea.

Also, using ASC for HASH indexes, HASH keys, and PRIMARY KEY is not meaningful and will be ignored. It’s best to leave that out to not confuse other developers who may read that code.

You can of course create a new table with all the indexes/keys you want, then use INSERT … SELECT … to move the data to the new table, then drop the old table and rename the new table to use the old name. That could take a while, and the data would need to be offline during the copying and renaming process. So it is not a great solution but it may be a reasonable alternative.

3

Someone asked me about this today. That CREATE INDEX command works in the current shipping version in SingleStoreDB.