On Thu, Jun 08, 2006 at 02:00:43PM +0200, Davide Alberani wrote: Just a couple of quick notes, for now.
> > 1) Not all the DBMS create indexes for foreign keys. PostgreSQL 8 > > and SQLite 3 don't, at least. > > I wasn't aware of this behavior. :-/ > Better to check if some foreign key are implicitly assumed to have > an index, and if they are required for good performances. AFAIK this is completely DBMS-dependent. > > 2) From SQLObject sources I discovered another point: the > > createTable method takes a boolean parameter createIndexes. I > > suggest that Davide (or someone with MySQL installed) do a quick > > test, by creating one or more tables containing foreign keys with > > tableClass.createTable(createIndexes=False) If indexes for the > > foreign keys are not created we should keep the ForeignKeys > > I'll check better later, but from a fast test I can say that the > Indexes _are_ created even with createIndexes set to False, for a > ForeignKey. > > I'm confused by this: this index is not in the Table.sqlmeta.indexes > list and - to tell the truth - I think is completely unused by MySQL which, > as far as I know, is not aware of the concept of Foreign Key (you can > insert whatever value you want, even if there's no corresponding entry > in the referred table.column) I am by no means a Mysql expert, but AFAIK the InnoDB backend supports referential integrity. And, quoting from the docs [http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html] InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. The index on the foreign key is created automatically. This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail. > > If they are created, likely they cannot be deleted (various reasons), > > Yep; first of all, they seems to be unnamed and appears to be > unreachables within SQLObject - so I won't spent much time on this. Yes. Situation is: foreign keys REQUIRE indexes under MySQL 5/InnoDB. This leads to unacceptable performance. So we REMOVE all foreign keys and use plain columns, instead. Is this correct? -- Giuseppe "Cowo" Corbelli ~\/~ My software: http://cowo.yoda2000.net -<! Take It All, Our Gold, Our Homes, Our Life, But We Didn't Kill Your Christ! !>- Blind Guardian - The Script For My Requiem _______________________________________________ Imdbpy-devel mailing list Imdbpy-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/imdbpy-devel
