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@example.com http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users