How to create a hash index on an existing table

Off-Topic
1

Hello team.
I’m facing this issue. I tried to create a hash index on an existing table with a few millions row.

create index idx_pod on METERING.T_CODA_ELABORAZIONE_POD_A(POD) using hash;

Unfortunately I run into the issue Error Code: 1706. Feature ‘Adding a non-unique hash index’ is not supported by MemSQL.

On the contrary the following statement works like a charm (see the last line)

CREATE TABLE METERING.T_CODA_ELABORAZIONE_POD(
ID_ENTRY_Q  bigint(20) NOT NULL AUTO_INCREMENT,
CODA VARCHAR(100),
POD VARCHAR(20),
ID_OPERATION numeric(10,0),
TYPE_OPERATION varchar(100),
DATAINSERIMENTO datetime,
ISPROCESSED char(1) default  '0',
LOGMESSAGE Varchar(4000),
DATAELABORAZIONE datetime,
DATAINIZIALE date,
	CONSTRAINT T_CODA_ELABORAZIONE_POD_PK PRIMARY KEY (ID_ENTRY_Q),
        KEY `hashindex` (`POD`) USING HASH
 )          ;

How can I create a hash index on an existing table? Isn’t that possible? It doesn’t seem so actually. Can I work it around any way?

Thanks.

2

Hi Marco,

We don’t support alter tables to add hash indexes to rowstore tables today. I would recommending using a skiplist index (the performance difference between hash indexes and skiplists is typically only 10 to 20%)

-Adam