Re: all foreign key should have index?
From: Murdoc <murdoc_0_at_hotmail.com>
Date: Fri, 3 Feb 2006 10:51:52 +0000 (UTC)
Message-ID: <xn0ei1x4zp988000_at_news-south.connect.com.au>
> >> On Wed, 1 Feb 2006 20:17:11 +0000 (UTC), "Murdoc"
> >> <murdoc_0_at_hotmail.com> wrote:
> >>
> >> > Gene Wirchenko wrote:
> >> >
> >> >> On Tue, 31 Jan 2006 22:12:56 +0000, Eric Junkermann
> >> >> <eric_at_deptj.demon.co.uk> wrote:
> >> >>
> >> >> [snip]
> >> >>
> >> >> > When you delete a parent row, or update its key, the DBMS needs to find
> >> >> > the children, either to cascade the operation or to forbid it - how can
> >> >> > it do this efficiently without an index? But of course if you never do
> >> >> > those things,
> >> >> > you might still need it to find child rows efficiently anyway.
> >> >>
> >> >> Why does it have to be an index?
> >>
> >> > Generally: efficiency. If you want the DB to enforce referential integrity, the operation of
> >> > doing so needs to be efficient. A search of the entire 'FK' table to ensure that a record can
> >> > be deleted from the 'PK' table is (a) inefficient; and (b) pointless. Or even worse, on a
> >> > cascade delete. The search (when using an index) really comes down to "Is there an entry in
> >> > not going to have a large performance gain with an index (or maybe a performance detriment).
> >>
> >> That is not the point. Why does it have to be an INDEX?
> >>
> >> You have been assuming that an index is the only way. It is not.
Date: Fri, 3 Feb 2006 10:51:52 +0000 (UTC)
Message-ID: <xn0ei1x4zp988000_at_news-south.connect.com.au>
Christopher Browne wrote:
> > Gene Wirchenko wrote: > >
> >> On Wed, 1 Feb 2006 20:17:11 +0000 (UTC), "Murdoc"
> >> <murdoc_0_at_hotmail.com> wrote:
> >>
> >> > Gene Wirchenko wrote:
> >> >
> >> >> On Tue, 31 Jan 2006 22:12:56 +0000, Eric Junkermann
> >> >> <eric_at_deptj.demon.co.uk> wrote:
> >> >>
> >> >> [snip]
> >> >>
> >> >> > When you delete a parent row, or update its key, the DBMS needs to find
> >> >> > the children, either to cascade the operation or to forbid it - how can
> >> >> > it do this efficiently without an index? But of course if you never do
> >> >> > those things,
> >> >> > you might still need it to find child rows efficiently anyway.
> >> >>
> >> >> Why does it have to be an index?
> >>
> >> > Generally: efficiency. If you want the DB to enforce referential integrity, the operation of
> >> > doing so needs to be efficient. A search of the entire 'FK' table to ensure that a record can
> >> > be deleted from the 'PK' table is (a) inefficient; and (b) pointless. Or even worse, on a
> >> > cascade delete. The search (when using an index) really comes down to "Is there an entry in
> the >> > index for this field value?".> >> > Again, it does come down to the size of the table. A table with a maximum of about 10 rows is
> >> >
> >> > not going to have a large performance gain with an index (or maybe a performance detriment).
> >>
> >> That is not the point. Why does it have to be an INDEX?
> >>
> >> You have been assuming that an index is the only way. It is not.
> > > > Then what is the other way? What other mechanism allows efficient > > searching of a table without a full search? > > Sometimes, if the table is fairly small, a full search is in fact the > fastest method. Pointedly, if the table is likely to fit in one page, > then a full search guarantees finding the result with one disk access. > In comparison, any index-like indirection would require two disk > accesses, and therefore be more expensive than full search. Indeed, > the result of that is that full scan is "probably optimal" if the > table fits into two pages of disk.
In my experience, any table that is that small is rarely the subject of a FK search. It is usually the FK in the larger tables that requires an index.
> Some people assume that "index" is synonymous with "B-tree";
An index is just a mechanism to locate one or more records quickly.
> there are > other data structures that they may not believe should be called an > "index." Pointedly, hashing is a sometimes-efficient mechanism for > searching for data. (It tends to seem less attractive when applied to > disk-based structures than it is for in-memory data, but that's noth > particularly relevant here...)
And I am not one of these people. Indexes can be created with any data structure in mind. A developer/database designer may even decide to create their own index using database tables (something that we had to do).
--Received on Fri Feb 03 2006 - 11:51:52 CET