If your simpleId is a concatenation of player and friend then I'd suggest to remove that colunm and add unique constraint on pair of columns (player, friend). If your simpleId is not the same then remove it anyway, add column current_user and add unique constraint on (current_user, friend). After these changes the following query should be fast for you:
select a.* from globalRankingTable a, friendTable b where b.current_user = ?1 and b.friend = a.name; Pavel On Thu, May 6, 2010 at 5:35 AM, Ian Hardingham <[email protected]> wrote: > Hey guys. > > I have an 1000 row table that looks like this: > > CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY > AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, > record TEXT); > > And a "friends" table which looks like this: > > CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY > AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT); > > I need to: > > Select all from globalRankingTable > > Go through each row > > Find whether "name" is a "friend" of my current user (ie, does name > concatinated with username appear as "simpleid" in friendTable) > > At the moment, this is really slow - can anyone suggest optimisations? > I'm pretty new to dbs. > > Thanks, > Ian > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
