Re: composite key question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 09/18/04 

Date: Sat, 18 Sep 2004 22:33:02 +0200

On Sat, 18 Sep 2004 10:45:21 -0700, Stefan Berglund wrote:

>On Thu, 16 Sep 2004 23:09:25 +0200, Hugo Kornelis
><hugo@pe_NO_rFact.in_SPAM_fo> wrote:

(snip)

>>You don't need an extra index, but you do need to redefine your existing
>>index: instead of
>> PRIMARY KEY (id, library_id)
>>it should read
>> PRIMARY KEY (library_id, id)
>>
>>This way, the index associated with the primary key will be suitable for
>>finding the maximum id for a specific library_id (1 in your example
>>query).

(snip)

>Hugo, would you mind elaborating on this or pointing me to an
>article that explains why this is desirable. I don't understand
>why you state that "the index associated with the primary key
>will be suitable for finding the maximum id for a specific
>library_id". Does it have to do with the magnitude of the
>numbers involved or something else entirely?

Hi Stefan,

Something else entirely. I'll use an analogy to explain.

Imagine I give you the phone book of the Dutch city Amsterdam. All entries
in this phone book are ordered by last name; people with the same surname
are ordered by streetname.

If I'd ask you for the alphabetically last street name in Amsterdam where
someone named "Jansen" lives, you'd quickly find the "J" entries, skip to
a page containing entries for "Jansen", find the last entry and tell me
the street name of this entry. Should be possible in less than a minute.

Now I'm gonna ask you for the alphabetically last surname of all people
living in the Kalverstraat (a street in Amsterdam). What can you do? The
ordering applied to the entries in the phone book won't help you. The best
way to do this is to start at the last page and read back until you
encounter an entry with street Kalverstraat - this will be the
alphabetically last person on the Kalverstraat. Will take you several
minutes if you're lucky, hours if you're unlucky.

The same applies to Greg's problem. If the PK is defined (lib_id, id), a
search for the highest id of a given lib_id can be accomplished by
locating an entry with the correct lib_id and using that as a start point
for locating the highest id with that lib_id. But if the PK is defined as
(id, lib_id), all index entries have to be scanned (unless SQL Server is
capable of scanning an index backwards and the optimizer realizes that
this would be a better plan for this query - and even in that case, it
would still be slower).

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • copy auto_increment value to another field
    ... i want to create a unique serial number to my Db entries. ... the best way would be to add the auto_increment primary key value to a ... data first, then get the last inserted id value and then update the ... entry with the $serial_num variable? ...
    (comp.lang.php)
  • Duplicate entries
    ... mainternance but I want to avoid duplicate entries except in one type ... using the caseref and Area as a combined Primary key but in another ... would appear to the database as a duplicate entry. ...
    (microsoft.public.access.formscoding)
  • Re: Cant call method "get_value....
    ... i have a perl script that supposed to add users to  ldap. ... my $entry = pop @entries; ... My bet is that if you check @entries you will find it is empty. ...
    (perl.beginners)
  • Re: Solving the lib mismatch problem
    ... This section is pointed to by an entry in the section table with the ... The number of entries in the table is determined by DT_VERDEFNUM. ... This is an index into the string section referenced in the section ... Offset in the string section reference by the link in the section ...
    (comp.unix.programmer)
  • Hollywood Book Festival - Call For Entries
    ... 2006 Hollywood Book Festival - Call for Entries ... Hollywood Book Festival submissions cannot be returned. ... Each entry package must contain the official entry form or facsimile, ... Applications must be accompanied by a non-refundable entry fee via ...
    (alt.fan.harry-potter)