If you had the following tables
Table PEOPLERANKINGS(personid INTEGER PRIMARY KEY, personname TEXT, ranking)
Table FRIENDS(personid1, personid2)
and an index on
PEOPLERANKINGS.ranking
and FRIENDS.personid1,FRIENDS.personid2 is a composite unique primary key
You could get the top 10 ranked people
select * from PEOPLERANKINGS order by ranking desc limit 10
and get your own ranking and the ranking of your friends:
select peoplerankings.* from PEOPLERANKINGS
where personid IN
(select personid2 from FRIENDS where personid1 = ?yourId? )
NOTE: befriend yourself by default in the FRIENDS table.
Regards
Tim Romano
Swarthmore PA
On Fri, Jul 9, 2010 at 5:52 AM, Benoit Mortgat <mort...@gmail.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
