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);
The intention of this query is to, basically:
- get the top 10 scores in rankingTable
- get "my" score in rankingTable
- get all of my friends' scores in rankingTable
The query works, but is very slow. This may just be because it's a
complex query, but all feedback would be much appreciated.
For your extra information:
- globalRankingTable has several thousand rows
- friendTable has several thousand rows also, but each user will only
have 0-10 friends (usually on the low part of that scale)
- yes I know I'm an idiot with the upper stuff, a refactor is needed on
that - I know all of my "name" fields should really be integers.
Thanks,
Ian
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
