----- Forwarded by Ben Carlyle/AU/IRSA/Rail on 09/02/2004 12:04 PM -----
Ben Carlyle
09/02/2004 12:04 PM
To: Bertrand Mansion <[EMAIL PROTECTED]>@CORP
cc:
Subject: Re: [sqlite] Primary key and index
Bertrand,
Bertrand Mansion <[EMAIL PROTECTED]>
09/02/2004 04:29 AM
To: <[EMAIL PROTECTED]>
cc:
Subject: [sqlite] Primary key and index
> Does the declaration of an INTEGER PRIMARY KEY implies the creation of
an
> index ? I am asking because I have noticed a performance boost when I
create
> an index on an INTEGER PRIMARY KEY when sorting rows with the primary
key
> column.
Yes, but not in the way you're thinking.
> More generally, does declaring any column primary key implies that this
> column will be indexed ? I am not sure about that because when I do a
PRAGMA
> index_list(), there is no index shown for the primary key.
Yes, although an INTEGER PRIMARY KEY is a special case of this.
> Thanks for any hints,
>From http://www.sqlite.org/lang.html#createtable:
"Specifying a PRIMARY KEY normally just creates a UNIQUE index on the
primary key. However, if primary key is on a single column that has
datatype INTEGER, then that column is used internally as the actual key of
the B-Tree for the table. This means that the column may only hold unique
integer values. (Except for this one case, SQLite ignores the datatype
specification of columns and allows any kind of data to be put in a column
regardless of its declared datatype.) If a table does not have an INTEGER
PRIMARY KEY column, then the B-Tree key will be a automatically generated
integer. The B-Tree key for a row can always be accessed using one of the
special names "ROWID", "OID", or "_ROWID_". This is true regardless of whether or not
there is an INTEGER PRIMARY
KEY."
i.e.:
1 Table = 1 BTree, the BTree holds the data and is ordered by ROWID
1 Table with 1 Index = 2 BTrees, the second referring to rows in the first
1 Table with PRIMARY KEY = 1 Table with 1 (unique) Index
1 Table with INTEGER PRIMARY KEY = 1 Table, with its own BTree forming its
unique index
Benjamin.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
