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]
-----------------------------------------------------------------------------
