Anthony Cooke wrote: > If table a is person_entry and table b is person_account and you will be > searching according to an exact comparison then why use a CHAR field for the > ID instead of a numerical equivalent? > > Why not normalize so that each record in "entries" would have one Unique > autoincrement number, index upon it and use this as a foreign key across all > tables? Indexes of this kind are much smaller and faster than their > character counterparts. MySQL excels with these. The tables are normalized, and yes, it would be possible to use the autoincrement integers as foreign keys instead of character ids. The character ids are used because the original databases are imported from external sources, and those sources do use character ids. The small, fixed-length, indexed character columns are supposed to be pretty fast, and I need to report those ids to the users anyway. So I am not sure just how much speed gain would the integeres give me. > > > >From my point of view most of the queries would be to pull the attributes > available from the entries table, and then pulling from each dimension. This > would happen in each collection of objects. > > If cross referencing is key then all tables would benefit from using foreign > keys. My two cents. Would like to hear your point of view. Do you mean, putting primary entry identifiers of all 4 databases into each table instead of using the cross-reference tables? That won't work because in general there is a many-to-many relationship between entries in my databases. For many-to-many relationships, one has to have the cross-reference tables. Thank you for thinking about this problem of mine :). I am doing a rewrite of my search engine, so I may be able to introduce some optimizations into both the database and the code. But we just thought that putting the entire thing into RAM might give us more speed gain than all of the little tweaks one can think of combined. Regards: Yury
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
