unique_together and composite columns indexing

Hi all,

if I'm not mistaken, if I have a model with unique
constraint
'unique_together', Django does not support composite index
of related
columns, i.e.

class AModel (models.Model):
    col_a = models.CharField(maxlength=20, db_index=True)
    col_b = models.CharField(maxlength=20, db_index=True)
    class Meta:
        unique_together = (('col_a','col_b'),)

generates SQL commands:

CREATE TABLE `xxx_amodel` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `col_a` varchar(20) NOT NULL,
    `col_b` varchar(20) NOT NULL,
    UNIQUE (`col_a`, `col_b`)
);
CREATE INDEX `xxx_amodel_col_a` ON `wss_amodel` (`col_a`);
CREATE INDEX `xxx_amodel_col_b` ON `wss_amodel` (`col_b`);


Should not be there another meta command, e.g.

        indexed_together = (('col_a','col_b'),)

producing index

CREATE INDEX `xxx_amodel_col_a_col_b`
   ON `wss_amodel` (`col_a`,`col_b`);


Peter

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

Peter Melvyn skrev:
> Hi all,
>
> if I'm not mistaken, if I have a model with unique
constraint
> 'unique_together', Django does not support composite
index of related
> columns, i.e.
>
> class AModel (models.Model):
>     col_a = models.CharField(maxlength=20,
db_index=True)
>     col_b = models.CharField(maxlength=20,
db_index=True)
>     class Meta:
>         unique_together = (('col_a','col_b'),)
>
> generates SQL commands:
>
> CREATE TABLE `xxx_amodel` (
>     `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
>     `col_a` varchar(20) NOT NULL,
>     `col_b` varchar(20) NOT NULL,
>     UNIQUE (`col_a`, `col_b`)
> );
> CREATE INDEX `xxx_amodel_col_a` ON `wss_amodel`
(`col_a`);
> CREATE INDEX `xxx_amodel_col_b` ON `wss_amodel`
(`col_b`);
>
>
> Should not be there another meta command, e.g.
>
>         indexed_together = (('col_a','col_b'),)
>
> producing index
>
> CREATE INDEX `xxx_amodel_col_a_col_b`
>    ON `wss_amodel` (`col_a`,`col_b`);

Postgresql will implicitly generate this index when it
encounters the
UNIQUE constraint. My guess is that other backends will do
the same.

A more interesting case is when you want to create
multifield indices
which aren't unique. Your syntax could be used for this.

Nis






--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

On 8/30/07, Nis Jørgensen <nissuperlativ.dk> wrote:

> > CREATE INDEX `xxx_amodel_col_a_col_b`
> >    ON `wss_amodel` (`col_a`,`col_b`);
>
> Postgresql will implicitly generate this index when it
encounters the
> UNIQUE constraint. My guess is that other backends will
do the same.

Are you sure? I don't have PostgreSQL installed, but it
seems that
SQLite3 and MySQL5 do not create a composite index on
UNIQUE
constraint automatically:

1.a If I inspect sqlite_master table or use .indices
command, there is no
       composite index
1.b If I create a composite index by explicit command, it
appears in
       sqlite_master table and is listed by .indices
command

2.a If I let MySQL to explain a select command from such
table, there is no
       composite index listed
2.a If I create a composite index by explicit command, it is
listed by explain
       command among available indices

Peter

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

On 8/30/07, Peter Melvyn <peter.melvyngmail.com> wrote:

> 2.a If I let MySQL to explain a select command from
such table, there is no
>        composite index listed
> 2.a If I create a composite index by explicit command,
it is listed by explain
>        command among available indices

You are right: I inspected indices using SHOW INDEX FOR ....
command
and I can see now there is really composite index created
internally.

Peter

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---