Creating table with Unique index

Hello team

I’m making transfer from MariaDB to memSQL
in MariaDB i have a little table
CREATE TABLE l_phones (
id INT(11) NOT NULL AUTO_INCREMENT,
l_id INT(11) NOT NULL,
phone BIGINT(20) NOT NULL,
params TEXT NULL DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE INDEX uindex (l_id, phone)
)
COLLATE=‘utf8_general_ci’;

and i’m trying to create same table in memSQL
CREATE TABLE l_phones (
id INT(11) NOT NULL AUTO_INCREMENT,
l_id INT(11) DEFAULT 0,
phone BIGINT(20) DEFAULT 0,
params TEXT NULL DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE INDEX uniqueIndex (l_id, phone)
)
COLLATE=‘utf8_general_ci’
ENGINE=MyISAM
;

but as result - i have error message

12:43:53: ERROR 1895 ER_MEMSQL_UNIQUE_KEY_IMPLICIT_SHARD_KEY: The unique key named: ‘uniqueIndex’ must contain all columns specified in the primary key when no shard key is declared

i read manual, i can see some shard key, but this manual so little - i cant understand my mistake

How can i modify this query to create correct table with unique index?

Thanks

Hi Dennis,

There is some info in the remarks section of the create table documentation:

this part:

’ MemSQL supports primary/unique keys only if the key contains all columns in the shard key. For more information about the shard key, see Distributed SQL.’

The high level reason for this restriction is that MemSQL will shard your data using the primary key (if you don’t specify a separate shard key) and once sharded on that key it becomes expensive to verify uniqueness of other keys that don’t contain the shard key. The database would need to query all other shards to check if it had a duplicate value.

We may remove this restriction in a future release. For now, your only option is to change your unique key into a regular key. If the table is small and rarely changes you could also consider making it a reference table (SingleStoreDB Cloud · SingleStore Documentation). Reference tables support unique keys since they are single-host tables (no sharding involved).

Adam, personally you can create SQL query for creating table with Unique index?!

Like i see from your response - you can not…

because you sent me to docs…

You docs not best, so because i’m asking for help…

And it will be more useful if somebody refactor my example to correct.

Sorry, but You complicated Unique index.
And if you will have more example - you will have more users, because on example - easy understanding.

Can you please show - how should i refactor my SQL?

Thanks

PS: reading of MySQL docs - its not easy too… so dont make same mistake

Hi Dennis,

No, you can’t create that unique index for the reasons I mentioned above (your shard key column ‘id’ isn’t part of your unique key (l_id, phone) - this is the restriction memsql puts on unique keys today).

The two options I mentioned in my first response are:

  1. Don’t have a unique key index and leave your table as a sharded table (by default all tables in MemSQL are sharded based on the primary key unless a SHARD KEY is specified). Instead create a normal non-unique secondary key:

CREATE TABLE l_phones (
id INT(11) NOT NULL AUTO_INCREMENT,
l_id INT(11) NOT NULL,
phone BIGINT(20) NOT NULL,
params TEXT NULL DEFAULT NULL,
PRIMARY KEY ( id ),
KEY uindex ( l_id , phone ))
COLLATE=‘utf8_general_ci’;

  1. Create your table as a reference table

    CREATE REFERENCE TABLE l_phones (
    id INT(11) NOT NULL AUTO_INCREMENT,
    l_id INT(11) NOT NULL,
    phone BIGINT(20) NOT NULL,
    params TEXT NULL DEFAULT NULL,
    PRIMARY KEY ( id ),
    UNIQUE INDEX uindex ( l_id , phone ))
    COLLATE=‘utf8_general_ci’;

I would read the docs I linked above about REFERENCE tables to decide if that is a good idea or not for what your trying to do. REFERENCE tables are copied (they are replicated to) every node in the cluster. If the table is large that will use up a lot of memory on your cluster (and writing to reference tables is much slower then a sharded table).

-Adam

Thanks for that, i need to study info about REFERENCE tables for understanding how its useful for me

But i think your default method to create UNIQUE INDEX just with primary key - its bad idea. I dont know - what should happent to make duplicate of primary key (with authoincrement). Cant understand a reason of your changing of reason of using UNIQUE INDEX, you made it harder.

I read about REFERENCE tables, there is tables with a small data set.
My tables with a unique index can contain millions of records, and at the same time, the uniqueness of the index should be checked.

I still can’t put in my mind your logic of working with unique fields, but I ask for help in creating a table in which there will be:

  • many millions of records
  • several unique indexes (all of them will NOT contain the main key)
  • the type of fields may be different

Is it possible to implement a standard table with unique indexes with such conditions, or do I have one way - REFERENCE tables?

PS: for now - i’m planning to use one server, but in future i will create a cluster

Hi Dennis,

Unfortunately, we don’t support a way of creating such a table today outside of making it a reference table. Our unique key support is pretty limited right now.

-Adam

Strange, maybe I’m those one developer who is using UNIQUE INDEX in MySQL/MariaDB, but such INDEX i’m using in different commerce projects

But my experience is showing, that its most quickly and reliable method to have JUST UNIQUE data in table.

Maybe you have UNIQUE INDEX in roadmap soon?!

Hi Dennis,

Your not the only one to encounter this limitation. More general support for unique secondary keys is on our roadmap, but I’m not sure on a timeline right now.

-Adam

Is your roadmap available to us or is this classified information?