On Jun 07, Giuseppe Cowo Corbelli <[EMAIL PROTECTED]> wrote:
> 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.
> 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)
> 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.
> so we can stay with the current CVS (no Foreign Keys, just plain
> data).
Makes sense; actually there are few foreign keys, but I think they
doesn't greatly affect performances.
> Just a couple of reminders: primary keys may be already indexed by
> default (PostgreSQL does do this, SQLite doesn't);
Duh! Thanks for this notice: this is another thing to keep in mind.
> CastInfo
> personIDIdx = DatabaseIndex(personID) movieIDIdx =
> DatabaseIndex(movieID)
>
> OK! It's likely you search this table when you already know what
> person and movie you are talking about.
Yes, from what I can tell, there are no exceptions to this rule: you
get data from CastInfo _only_ when you know a movieID or a personID.
> Name
> pNameIdx = DatabaseIndex({'column': name, 'length': 5},
> imdbIndex) namePcodeCfIdx = DatabaseIndex(namePcodeCf)
> namePcodeNfIdx = DatabaseIndex(namePcodeNf) surnamePcodeIdx =
> DatabaseIndex(surnamePcode)
>
> OK, with 2 points: 1) pNameIdx, I think it would be better
> to index the uppercased or lowercased column, and search
> for the up or low case. It seems that this can be done with
> {'expression':'lower(name)'} but I am still unable to get it working
Wait: does we need these "pNameIdx" and "mTitleIdx" indexes at all?
Actually we need to search the "name" or the "title" column (and "imdbIndex"
and friends) only in a specific case: substituting strings in text fields.
E.g.: if in the "plot" information theres a line like that:
... and she worked with the great actor 'Gino Pino (II)' (qv) ...
the Name table is scanned for the row with name set to "Gino Pino"
and imdbIndex set to "II"; its personID is returned and a Person
object instanced.
In this case, searching for person names/movie titles we _always_
know the complete and correct name (upper/lower case included).
So: this indexes are probably needed (and as you suggested it's better
to split them in separate indexes); I'm wondering if we can just
index the "name"/"title" column and what's the best length of text
to be considered (5 was choose following my mood of the time, I fear).
> 2) The table is usually searched for a specific imdbID? If so a
> imdbIDIdx = DatabaseIndex(imdbID) would be good
As far as I can tell, no; the only time it's needed is converting
a movieID _to_ an imdbID.
--
Davide Alberani <[EMAIL PROTECTED]> [PGP KeyID: 0x465BFD47]
http://erlug.linux.it/~da/
_______________________________________________
Imdbpy-devel mailing list
Imdbpy-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/imdbpy-devel
