On Fri, Jul 9, 2010 at 11:08, Ian Hardingham <i...@omroth.com> wrote:
> Hey guys.
>
> I have a query which is very slow, and was wondering if there was any
> advice you guys had on it.
>
> Here are two table definitions:
>
> CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY
> AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL,
> record TEXT);
>
> CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY
> AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT);
>
> And here is my query (written in a script language):
>
> db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE
> upper(name) = upper('?') OR id < ? union all SELECT a.* FROM
> globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?')
> AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0,
> %client.username, %globId, %client.username);
>Create an index either on player column or friend column in your second table. CREATE INDEX i_friendTable ON friendTable (player ASC); -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
