In the last episode (Mar 05), Daevid Vincent said:
> > I'm not sure that sentence means what you think it does. What
> > they're saying is you need to index both fields, and if you decide
> > to make that index a compound one with multiple keyparts, the
> > foreign/referenced field must be the first. They don't have to be
> > the first fields in the table or anything.
>
> Right, but sometimes it isn't, or you already used up that 'first'
> spot for a different foreign key reference in another table. Maybe I
If it isn't, then create another index, on just that field. If you
have multiple foreign keys, create a separate index, one for each
foreign key. You can have multiple indexes on one table with no
problems.
Maybe MySQL should be modified so that the required indexes are
silently created when a FOREIGN KEY clause is processed.
> am still not understanding something elementary, but I've tried to
> create some tables, and certain ones work, while others don't, and
> they're always related to that foreign key issue. In any event, it
> shouldn't matter if it's first or not! I should be able to use
> any/all fields in a any number of databases as foreign keys
> reguardless of their position in the schema or index.
>
> > As for why you want them indexed. Have you ever tried deleting a
> > lot of records from a table with a foreign key constraint on
> > another table with no index? Each delete of your first table
> > requires a full table scan of the second table, to make sure you're
> > not violating the constraint. A co-worker forgot to index a
> > constraint in Oracle once and his table updates took 2 hours
> > instead of 2 minutes.
>
> I don't dispute the benefit of indexes, I just don't think it should
> be a REQUIREMENT. And to be honest, sometimes a table isn't made of
> millions of records, maybe it's only a few, but you still want ref
> integrity.
If it's only a few records then the index won't take up that much space
:)
I'm pretty sure the requirement was done to make the InnoDB coders'
lives easier. I know there are low-level functions for "look up a
keyvalue in an index". I don't know if there is one for "do a full
table scan and search for a value in one field" without having to
generate a small query and execute it (i.e. subquery, which mysql does
not yet support).
--
Dan Nelson
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
