RE: [sqlite] Query Optimizer and INTEGER PRIMARY KEY

PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: martes, 19 de diciembre de 2006 16:48 Para: sqlite-users@sqlite.org Asunto: Re: [sqlite] Query Optimizer and INTEGER PRIMARY KEY Dennis Cote <[EMAIL PROTECTED]> wrote: > Jesús López wrote: > > Given the table: > > &

RE: [sqlite] Query Optimizer and INTEGER PRIMARY KEY

: sqlite-users@sqlite.org Asunto: Re: [sqlite] Query Optimizer and INTEGER PRIMARY KEY Jesús López wrote: > Given the table: > > CREATE TABLE Posts ( > PostID INTEGER PRIMARY KEY, > CategoryID INT NOT NULL > ) > > And the index: > > CREATE UNIQUE INDE

Re: [sqlite] Query Optimizer and INTEGER PRIMARY KEY

[EMAIL PROTECTED] wrote: In SQLite, every index has the INTEGER PRIMARY KEY as an implied final column. So an index on Post(CatagoryID, PostID) Is really an index like this: Post(CategoryID, PostID, PostID) In other words, the extra PostID column is redundant. If you omit it all

Re: [sqlite] Query Optimizer and INTEGER PRIMARY KEY

Dennis Cote <[EMAIL PROTECTED]> wrote: > Jesús López wrote: > > Given the table: > > > > CREATE TABLE Posts ( > > PostID INTEGER PRIMARY KEY, > > CategoryID INT NOT NULL > > ) > > > > And the index: > > > > CREATE UNIQUE INDEX IX_Posts (CategoryID, PostID) > > > > Your create index

Re: [sqlite] Query Optimizer and INTEGER PRIMARY KEY

Jesús López wrote: Given the table: CREATE TABLE Posts ( PostID INTEGER PRIMARY KEY, CategoryID INT NOT NULL ) And the index: CREATE UNIQUE INDEX IX_Posts (CategoryID, PostID) The query: SELECT CategoryID, PostID FROM Posts ORDER BY CategoryID LIMIT 10 Takes about 15 ms to

[sqlite] Query Optimizer and INTEGER PRIMARY KEY

Hi, Given the table: CREATE TABLE Posts ( PostID INTEGER PRIMARY KEY, CategoryID INT NOT NULL ) Filled up with 500,000 rows And the index: CREATE UNIQUE INDEX IX_Posts (CategoryID, PostID) The query: SELECT CategoryID, PostID FROM Posts ORDER BY CategoryID LIMIT 10 Takes about