Comment 1 for bug 660038

But I tend to agree with InnoDB way..because it perfectly makes sence for me
By theory, Primary Key is not a constraint but a way to address a particular
row (tuple) in a table (relation).
So primary key is the logical address of a row (tuple).
(But ofcource it is implimented in RDBMS systems as a constraint.)

if you have any other logical address..(same example of the 6 byte key in
your case),
why don't you make it explicity primary key?

being an Oracle DBA, I know oracles approach is entirely different.
But i am not a fan of that.

I am not the right person to say.. but to my understanding, Drizzle is not
going to change the fundamentals of any storage engine.
Probably there will be a different storage engine which stores tuples in
different way like you said.
can you check whether PBXT or BlitzDB fits your requirement?

Thank you,
Jobin.

On Wed, Oct 13, 2010 at 9:56 PM, Swany <email address hidden> wrote:

> Public bug reported:
>
> InnoDB always organizes data (on disk) around the PRIMARY KEY, and
> failing that, around the first NOT NULL UNIQUE index on the table.
>
> There are times when this organization is undesirable, particularly when
> there is the need for an identifying constraint (PRIMARY KEY or UNIQUE
> NOT NULL) but this constraint is long (in terms of bytes). This long
> clustered key results in long secondary keys.
>
> In these cases I would like to be able to force an internal 6 byte key.
>
> I suggest:
> CREATE TABLE t1 (
> c1 char(64) not null,
> c2 char(64) not null,
> c3 bigint not null,
> primary key (c1,c2),
> unique(c3)
> ) CLUSTERED INTERNALLY;
>
> and
>
> CREATE TABLE t1 (
> c1 char(64) not null,
> c2 char(64) not null,
> c3 bigint not null,
> primary key (c1,c2),
> unique(c3)
> ) CLUSTERED USING c3;
>
> The default should be to cluster using the primary key, but the cluster
> index should be able to be chosen manually, as long as it meets the
> requirements for a clustered key.
>
> See also: http://bugs.mysql.com/bug.php?id=57358
>
> ** Affects: drizzle
> Importance: Undecided
> Status: New
>
> --
> Add CREATE TABLE option to force InnoDB to use a short 6 byte internal key
> https://bugs.launchpad.net/bugs/660038
> You received this bug notification because you are a member of Drizzle-
> developers, which is subscribed to Drizzle.
>
> Status in A Lightweight SQL Database for Cloud Infrastructure and Web
> Applications: New
>
> Bug description:
> InnoDB always organizes data (on disk) around the PRIMARY KEY, and failing
> that, around the first NOT NULL UNIQUE index on the table.
>
> There are times when this organization is undesirable, particularly when
> there is the need for an identifying constraint (PRIMARY KEY or UNIQUE NOT
> NULL) but this constraint is long (in terms of bytes). This long clustered
> key results in long secondary keys.
>
> In these cases I would like to be able to force an internal 6 byte key.
>
> I suggest:
> CREATE TABLE t1 (
> c1 char(64) not null,
> c2 char(64) not null,
> c3 bigint not null,
> primary key (c1,c2),
> unique(c3)
> ) CLUSTERED INTERNALLY;
>
> and
>
> CREATE TABLE t1 (
> c1 char(64) not null,
> c2 char(64) not null,
> c3 bigint not null,
> primary key (c1,c2),
> unique(c3)
> ) CLUSTERED USING c3;
>
> The default should be to cluster using the primary key, but the cluster
> index should be able to be chosen manually, as long as it meets the
> requirements for a clustered key.
>
> See also: http://bugs.mysql.com/bug.php?id=57358
>
>
>