On Wed, 2003-11-05 at 13:44, Clark, Chris wrote: > > -----Original Message----- > > From: Mrs. Brisby [mailto:[EMAIL PROTECTED] > > > > MySQL has stated in-documentation that it uses a B-tree for > > it's index. > > I think this is a mistake- especially for larger indexes. > > Using several B-trees attached to a hash-table is much faster > > (if the hash is fast or your data is uniform). > > Following this train of thought (this isn't a feature request!); some DBMS's support > different structures for tables/indices and the DBA can specify the required > structure depending in the expected data usage/patterns (and a 2ndary index need not > be the same structure as the primary table structure, allowing for, say, a hash > table and b-tree 2ndary's as per the example above). E.g. Ingres has; Heap (yep, > completely unstructured), B-tree, Hash, and ISAM (there is also an R-tree but that > is only for spatial datatypes so it's not as interesting for this discussion). It > all depends on the data and how it is used as to which structure should/could be > used. > > A typical example of the hash primary and b-tree 2ndary is a unique customer id so > that the customer record can be hit directly with the hash, or if the hash is not > perfect, through a couple of overflow pages (compared to a b-tree which always will > need to jump through a few pages in the index, admittedly that may only be an > improvement of microsecs versus millisecs in lookup time). The b-tree 2ndary would > then be for things like customer name (which are often "duplicated", potentual for > lots of people called "Mr smith") in case one needs to perform searches on a > customer name (who say, forgot their customer id).
It wouldn't strictly require a hash-table either (after further thought); perhaps just the first octet of the key could be permuted though an 8->4 (15-bit) or 8->3 (7-bit) function. If the keys are otherwise random this should be just as well and you wouldn't lose the ability to use substrings... --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
