Great, thanks Simon.

Just how fast will my Select be?  Will it be order(n) with the number of 
records being returned?

Thanks,
Ian

On 22/09/2010 12:32, Simon Slavin wrote:
> On 22 Sep 2010, at 11:22am, Ian Hardingham wrote:
>
>> I have the following table:
>>
>> infPlayTable (id INTEGER PRIMARY KEY AUTOINCREMENT, infId INTEGER, name
>> TEXT NOT NULL UNIQUE, score REAL)
>>
>> I often need to do the following:
>>
>> SELECT name, score FROM infPlayTable WHERE infId = 670 ORDER BY score DESC
>>
>> What is the syntax for the index I need to create on InfPlayTable to
>> have that be quick?
> CREATE INDEX infScore ON infPlayTable (infld,score DESC)
>
> looks up the right records quickly.  Making the following index
>
> CREATE INDEX infScoreName ON infPlayTable (infld,score DESC,name)
>
> instead may make your application run faster due to how SQLite works 
> internally.
>
> Simon.

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to