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
