auto-inc column as secondary field to compose a primary key in mysql cluster

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

auto-inc column as secondary field to compose a primary key in mysql cluster

JohnXhark
I would like to define a primary key like this:  [shardingkey,  id(auto-inc)], is this possible? Here the id is a secondary field in primary key, but not the primary key by itself.

However, it's not clear if this is legitimate, the document at mysql.com says:
AUTO_INCREMENT columns.  Like other MySQL storage engines, the NDB storage engine can handle a maximum of one AUTO_INCREMENT column per table. However, in the case of a Cluster table with no explicit primary key, an AUTO_INCREMENT column is automatically defined and used as a “hidden” primary key. For this reason, you cannot define a table that has an explicit AUTO_INCREMENT column unless that column is also declared using thePRIMARY KEY option. Attempting to create a table with an AUTO_INCREMENT column that is not the table's primary key, and using the NDB storage engine, fails with an error.


Thanks,JX
--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: auto-inc column as secondary field to compose a primary key in mysql cluster

Mauritz Sundell
Hi John

On 2016-11-16 05:37, JohnXhark wrote:
> I would like to define a primary key like this:  [shardingkey,  id(auto-inc)], is this possible? Here the id is a secondary field in primary key, but not the primary key by itself.
>
> However, it's not clear if this is legitimate, the document at mysql.com says:
> AUTO_INCREMENT columns.  Like other MySQL storage engines, the NDB storage engine can handle a maximum of one AUTO_INCREMENT column per table. However, in the case of a Cluster table with no explicit primary key, an AUTO_INCREMENT column is automatically defined and used as a “hidden” primary key. For this reason, you cannot define a table that has an explicit AUTO_INCREMENT column unless that column is also declared using thePRIMARY KEY option. Attempting to create a table with an AUTO_INCREMENT column that is not the table's primary key, and using the NDB storage engine, fails with an error.
NDB allows one auto_increment column, and it must be part of an unique
key or primary key.
So, yes, you can have a primary key like you mention.

But, it might not behave as you expect, depending of what you expect:)

For example will id be unique with itself, not as for example with
MyISAM there id will be unique only for a specific sharding key.

Also I'm curious of how you use the sharding key, and why you need it.
Note, that having a non unique partition key like your sharding key,
puts the responsibility for keeping data balanced across partitions and
data nodes will be on the application and not on NDB.
If you have not a proven benefit by using a sharding key, my guess is
that the default partitioning on a primary key and an index on the
sharding key (now no longer a sharding key) might work better.
Best way is probably to test and measure what is best for your needs.

Regards,
Mauritz
>
>
> Thanks,JX


--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster

Loading...