Opened 18 years ago
Closed 16 years ago
#3030 closed enhancement (duplicate)
unique=True and db_index=True leads to duplicated indexes
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | trivial | Keywords: | db-be-api manage.py sqlall unique db_index |
Cc: | gabor@… | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | no |
Needs tests: | yes | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
At least in MySQL this produces a doubled index.
Example:
class Kunde(models.Model): kundenid = models.AutoField(primary_key=True) loginname = models.CharField(unique=True, maxlength=50, db_index=True)
produces:
CREATE TABLE `mgt_kunden` ( `kundenid`integer AUTO_INCREMENT NOT NULL PRIMARY_KEY, `loginname` varchar(50) NOT NULL UNIQUE ); CREATE UNIQUE INDEX mgt_kunden_loginname ON `mgt_kunden` (`loginname`);
So there is the automatically generated index from the UNIQUE keyword
and the additional index requested by db_index=True.
Attachments (1)
Change History (18)
comment:1 by , 18 years ago
comment:3 by , 18 years ago
Summary: | Models: Use of unique=True AND db_index=True at the same field should be flagged as an error → Models: unique=True should override db_index=True on (at least) MySQL |
---|
This definately generates incorrect SQL under MySQL. The UNIQUE constraint on the field itself produces an index named after the field, and the index created is named tablename_fieldname, so the result is two indexes. Using them both shouldn't be an error, I don't think - instead, the CREATE INDEX can simply be omitted (as an index is implicitly created by the UNIQUE constraint). No idea about other DB backends though.
comment:4 by , 18 years ago
Keywords: | unique db_index added |
---|---|
Summary: | Models: unique=True should override db_index=True on (at least) MySQL → unique=True and db_index=True leads to duplicated indexes |
Triage Stage: | Unreviewed → Accepted |
Both MySQL and SQLite doubles the index (can someone confirm on Postgres?), but doesn't complain, so you end up with these indexes:
PRIMARY PRIMARY 0 Edit Drop kundenid loginname UNIQUE 0 Edit Drop loginname t3030_kunde_loginname UNIQUE 0 Edit Drop loginname
This is fairly trivial, since it works, but does increase the key size (and therefore database size etc) and is not terribly elegant.
comment:5 by , 18 years ago
Actually - should db_index be creating a UNIQUE index at all? shouldn't it just be creating an INDEX (which is quite different)
comment:6 by , 18 years ago
Simon G: the db_index attribute checks to see if the index should be unique or not (so it examines the "unique" attribute as well). See django/core/management.py
at the top of the get_sql_indexes_for_model()
function for the details.
I think the small bug here is that the original code didn't take into account the fact that a "unique" constraint will construct an index in (almost) all databases because it is an efficient way of checking for uniqueness. So we may be able to short-circuit the index creation in those cases.
A fix for this ticket needs to check the behaviour of MySQL, PostgreSQL, SQLite (and preferably get some feedback on Oracle's behaviour as well) and confirm that they all create an index for "unique" fields. From memory, the answer is "yes", but I haven't explicitly checked it.
comment:7 by , 18 years ago
Oracle behaves the same way, emitting a (benign) error when it tries to create the redundant index.
comment:8 by , 18 years ago
Cc: | added |
---|
this is a problem with PostgreSQL too.
also, this is probably obvious, but nevertheless..
not just exlicit "db_index=True" fields are a problem (where as a 'fix' you can simply omit the db_index=True)
having a SlugField(unique=True) has the same problem, and in that case, there's no way to avoid it (except deleting the index manually in the database
comment:9 by , 17 years ago
Resolution: | → worksforme |
---|---|
Status: | new → closed |
I'm closing this because I believe it was fixed by the addition of the autoindexes_primary_keys backend feature back when the Oracle branch was merged in. Please re-open if that's not the case.
comment:10 by , 17 years ago
Resolution: | worksforme |
---|---|
Status: | closed → reopened |
I've test in r7411 and the problem persists. if unique=True and db_index=True, there is still redundant CREATE UNIQUE INDEX .... SQL command, because of MySQL creates on UNIQUE column declaration inherent one.
comment:11 by , 17 years ago
I verified on MySQL 5.0.37 using InnoDB engine that setting DatabaseFeature.autoindexes_primary_keys to True really solves this problem. But in the backend.mysql.base, this feature is set to False. I didn't care about MySQL before version 5.x so I don't know what features have its older versions.
by , 16 years ago
comment:12 by , 16 years ago
Has patch: | set |
---|---|
Needs tests: | set |
Because I don't know what was the behaveour of MySQL before version 5, I set DatabaseFeature?.autoindexes_primary_keys to True from MySQL server version 5++.
comment:13 by , 16 years ago
See also #5680 that takes the approach of simply doing away with the autoindexes_primary_key
DatabaseFeatures
property.
comment:14 by , 16 years ago
Sure: In #5680 "Oracle guys" have introduced the new DB Feature autoindexes_primary_key and used it in logic generating indeces. But they left its value for MySQL equal to False, hence it has no effect. My patch sets its value to True, if version of MySQL server (read in run-time) is >= 5. This is from backward compatibility reason, because I've ignored MySQL before version 5 per se and I have no expirience, what happen if this feature would be set True while running obsolete MySQL version.
comment:15 by , 16 years ago
Keywords: | db-be-api added |
---|
comment:16 by , 16 years ago
Component: | django-admin.py → Database wrapper |
---|
comment:17 by , 16 years ago
Resolution: | → duplicate |
---|---|
Status: | reopened → closed |
I'm going to close this in favour of the approach in #5680. Less options are better and all backends behave the same here (even MySQL 4.x).
ghaloo
class Kunde(models.Model):
is it at [url=http://shurl.org/SGqxN]abba[/url] mkay?!?!
Models: Use of unique=True AND db_index=True at the sa
Models: Use of unique=True AND db_index=True at the same field should be flagged as an error
Status: new
Reported by: fte@… Assigned to: adrian
Priority: low Milestone: Version 1.0
Component: django-admin.py Version: SVN
Severity: trivial Keywords: manage.py sqlall
Cc:
At least in MySQL this produces a doubled index.
Example:
class Kunde(models.Model):