Comment 2 for bug 660038

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 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
>>
>>
>>
>
> --
> 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 direct subscriber
> of the bug.
>