It is a valid point, but you need to do the math for the specific problem being resolved to decide how valid. With small index column sizes you need much more detail than I have at my fingertips to compute a real value, but let's say that you go to a 4 byte integer key. You lose 34 bytes from the index entry footprint, giving 50-34=16 bytes per entry plus some overheads. On an 8k page this gives you roughly 500 entries. At two levels in the B+ tree you address 250,000 rows. At three levels you can address 125,000,000 rows.

If you table space is expected to grow to 4,000,000 rows then you still have to expand the index to three levels, but you can go a lot further on three levels than you can with a larger key.

GUID's are ideal for distributed systems, but are not the most efficient way of handling centralized systems.

It's a matter of the favorite hammer. Sometimes you need a paintbrush, and a hammer just won't do. You can't let your (or my) favorite solution be the only solution you know because it is not appropriate for every situation.

----- Original Message -----
From: Alan McDonald
To: firebird-support@yahoogroups.com
Sent: Sunday, February 01, 2004 2:32 PM
Subject: RE: [firebird-support] MS SQL Vs. Firebird Again


> Using 8k page size and 50 character more or less keys+pointers
> entries, you get roughly 160 index entries per page. The first
> two layers of the B+ tree can address 160^2 records if the
> indeces are full. A third layer addresses 4,096,000 records. A
> max operation against an ascending index on a 4,000,000 row table
> should only need to read three pages maximum. This is consistent
> with the results I achieved in the random read tests. Note that
> my primary key is quite large - using an integer key would result
> in even greater efficiency in indexing.

So this is a small but valid argument against using GUID/UUIDs for the
primary key?
Alan



Yahoo! Groups Sponsor
ADVERTISEMENT





------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]