Hi, Doing some testing , creating a primary key with the INTEGER defined will not create an index but creating a PRIMARY KEY by it self does create an index
I.e Create table z(a PRIMARY KEY, B); creates an index but Create table z(a INTEGER PRIMARY KEY, B); does not So Yes I would be creating an index on the a column if it is an autonumber(identity ) column if I was using this column in joins or where clauses. regards Greg ----- Original Message ----- From: Michael Hunley To: Greg Obleshchuk Sent: Tuesday, February 10, 2004 6:49 AM Subject: Re: [inbox] Re: [sqlite] Primary key and index At 09:20 AM 2/9/2004 +1100, you wrote: >This is from the doc on the web > >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. > >So adding the Primary Key does create an index. > >you can verfiy this by doing > >create table a (b primary key,c); >select * from sqlite_master; So, then, is he seeing a false performance increase if he creates an index on the primary integer key? If not, and he is seeing a real performance boost, why isn't the index auto created? I.e. should we all be creating an extra index on our integer primary key tables for performance? thanks. michael
