List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:March 6 2003 3:25am
Subject:Re: Foreign keys and being FIRST index
View as plain text  
In the last episode (Mar 05), Daevid Vincent said:
> http://www.mysql.com/doc/en/SEC457.html states that "there must be an
> index where the foreign key and the referenced key are listed as the
> FIRST columns." Will this restriction be lifted soon? It is
> incredibly frustrating. I don't see why they have to be indexes, and
> more importantly, I don't see why they have to be FIRST! Ugh.

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.

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.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Foreign keys and being FIRST indexDaevid Vincent6 Mar
  • Re: Foreign keys and being FIRST indexDan Nelson6 Mar
    • RE: Foreign keys and being FIRST indexDaevid Vincent6 Mar
      • Re: Foreign keys and being FIRST indexDan Nelson6 Mar
      • RE: Foreign keys and being FIRST indexKeith C. Ivey6 Mar
        • Multiple foreign keys?Daevid Vincent8 Mar
          • re: Multiple foreign keys?Egor Egorov8 Mar
  • Re: Foreign keys and being FIRST indexWilliam R. Mussatto6 Mar