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?
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).
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:
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’;
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).
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
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.
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.