Thanks Dennis, Is it that when a Integer column of a table is defined as primary key, the it will be part of every index table (rather than rowid) defined on that table? How does it work when we define a non integer as primary key. Assuming in the example given below if we make the Title column as primary key and create index on Id, how does it affect the performance of the two queries? Regards, Phanisekahr
________________________________ From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thu 4/26/2007 7:52 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] An explanation? B V, Phanisekhar wrote: > Thanks for that Info. > > I have another question: > > Assume I have a table given below > "CREATE TABLE IF NOT EXISTS Title(Id INTEGER PRIMARY KEY, TitleName > String)" > "CREATE UNIQUE INDEX IF NOT EXISTS TitleIdx ON TitleName" > > Now since Id is an integer and a primary key, this will work as rowid > internally. > > I have two queries that needs to be optimized: > > 1 Select TitleName from Title where Id = ? > 2 Select Id from Title where TitleName = ? > > In order to make the previous two queries optimized, how should I > declare my Table and Index? > > Should it be: > > 1 > "CREATE TABLE IF NOT EXISTS Title(Id INTEGER PRIMARY KEY, TitleName > String)" > "CREATE UNIQUE INDEX IF NOT EXISTS TitleIdx ON (TitleName, Id)" > > 2 > The one which I assumed > > Which one of these will give the better performance for the two queries? > Or is there any other alternative that will give even better > performance? > > Regards, > Phanisekhar > Phanisekhar, Your original index definition is all that is needed. The index already contains the rowid for the table record, which happens to be the column id because of the integer primary key optimization. There is nothing to be gained by adding it to the index again. Your first query will be satisfied by a binary search in the title table looking for the id. It won't use the index. Your second query will be satisfied by a binary search in the TitleIdx index looking for a matching title. It won't use the Title table. HTH Dennis Cote ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
