--- [EMAIL PROTECTED] wrote:
> Robin Breathe <[EMAIL PROTECTED]> wrote:
> > Hugh Gibson wrote:
> > >> I'm intrigued. How do you get SQLite to use a multi-column index as it's
> > >> primary key (i.e. B-tree hash)? Please elaborate.
> > >
> > > Simply
> > >
> > > CREATE TABLE TransactionList (sTransactionID Text(13) DEFAULT
> > > '',sCommunityID Text(13) DEFAULT '',sObject Text(13) , PRIMARY KEY
> > > (sCommunityID, sTransactionID))
> >
> > Ah, but it's not used for the B-tree hash (at least not according to the
> > documentation).
> >
>
> The ROWID is always used as the btree key (not hash!) on the main
> database btree. But when you have a PRIMARY KEY a separate index
> btree is also created which uses the PRIMARY KEY as its key.
>
> Question: Why is this important to you?
I am not he, but if I were looking for ways to improve the file format
that's something I'd try to work in too. Both to save space and speed
things up.
CREATE TABLE(a PRIMARY KEY, b);
creates two btree structures:
(a || oid) -> NULL (the index)
(oid) -> (a || b) (the table)
So for every row, there are two copies of both "a" and "oid". Depending
on your schema, the space consumed by the table is from 0-100% more than
if we were able to create a single btree:
(a || oid) -> (b)
or even drop the oid altogether, it's not part of SQL anyway (is it?):
(a) -> (b)
As well as saving space, in the most common case an UPDATE or DELETE
would have to modify one less tree, and some SELECTs would open one
less tree structure.
Tricky to retain backward compatibility though.
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
