Add CREATE TABLE option to force InnoDB to use a short 6 byte internal key

[Swany]

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

[Jobin Augustine (jobinau)]

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,
...

[Swany (greenlion)]

Hi,

Summary tables in particular have this problem. A summary table may
have a very long (in terms of bytes) unique key or primary key, but
this causes a huge problem for secondary keys.

For example:
create table summary_table(
  summary_year int,
  summary_month int,
  summary_day int,
  summary_customer_id bigint,
  summary_customer_item_category int,
  summary_cnt bigint,
  summary_sum decimal(15,5),
  summary_min decimal(15,5)
  primary key( summary_year year,
  summary_month int,
  summary_day int,
  summary_customer_id bigint,
  summary_customer_item_category int ) ,
  key (summary_customer_id),
  key (summary_customer_item_category, summary_customer_id)
)

This table will be clustered on a 28 BYTE primary key. This is not efficient.

In this case, I want this table to have an internally clustered key.
The tables are maintained by software, and it doesn't make sense to
maintain auto_increment values on a summary table. That is ugly and
not efficient.

There are tons of other examples that I can think of where a long PK
is desirable but clustering on the PK is not.

I do not want to use a storage engine other than InnoDB.

Thank you for considering my feature request.

On Wed, Oct 13, 2010 at 11:14 AM, Jobin Augustine
<email address hidden> wrote:
> 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 ...

[Stewart Smith (stewart)]

On Wed, 13 Oct 2010 20:14:20 -0000, Swany <email address hidden> wrote:
> Summary tables in particular have this problem. A summary table may
> have a very long (in terms of bytes) unique key or primary key, but
> this causes a huge problem for secondary keys.

Only if you want to get at columns not in the primary key.

Using the pkey for the value in secondary indexes does let you get other
pkey values without getting the main record. This of course may/may not
be what you want.

> In this case, I want this table to have an internally clustered key.
> The tables are maintained by software, and it doesn't make sense to
> maintain auto_increment values on a summary table. That is ugly and
> not efficient.

This may not be too hard to add as an option.... but I'd only want to be
adding it to HailDB, not the innobase plugin.

--
Stewart Smith