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

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

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

JohnXhark
Hi Mauritz,

Thanks for your answering.

I found this from the doc:
"...an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column."

So my index definition will not work for innoDB, and it will not work as expected for MyISAM as you just said. it seems this is a mess. But, specifically, will it work as expected for NDB engine?

The motivation for my design is this:
we have a use case, heavily query of the pattern "select top (n) * from tbl where cityid = some_city and id > id1 and ... order by id", now assuming ndb is used as the engine, if pk is (cityid, id), then only a direct fetching is needed, however, if using schema like you mentioned: pk = id, index1 = (cityid, id), then a nested-loop query will be needed, and lots of network round-trip.

Strectly load balance is ideal, but not the top most concern. Also, I choose the wording "shardingkey" just for convenience, it's just a place holder for the left-most colomn here, so I can still leave the partitioning to mysql cluster.

JX

Sender:Mauritz Sundell <[hidden email]>

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
--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster
Loading...