sorting and indices

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

sorting and indices

Jelte Liebrand
Hi there,

Appologies if this is not the correct forum to send this mail to, but i
thought I'd start here as you guys probably know how to use sqlite the
best. If this is wrong, please let me know where i should be sending my
mail to...

I've recently created a little gaming server, which keeps track of
online highscores. So far i've just used gdbm to keep track of the
users, as all i really need is:
- unique user cookie (to identify him)
- user name
- user score

Howeveer, gdbm does not seem to be able to sort the records, so getting
a 'current rank' withing the score list is tricky. So I'm thinking of
changing this over to use sqlite.

In doing so i've started to wonder how best to do this. The user cookie
should be the primary key, but does sqlite support secondary keys?
If not, should i just put an index on the score column?

Ideally i'd like inserted records to be inserted based on their score.
That way i can just use the ROWID as their ranking. Otherwise i would
need to do something like:

select * from table order by score

And then count somehow how far down the list the user is to work out his
ranking. That sounds like a rather big performance hit, as i'd be
listing and looping through _all_ users just to get the ranking for one.
Surely there's a better way to use SQL to achieve this?

I thought secondary keys could do just that, but then i'm not an SQL
guru. So if anyone has any thoughts on how best to structure such a
table, i'd be most grateful.

(Note: every time the user updates his score i'd like to tell him what
his current rank is. Hence looping through all records would be too
costly i guess)

/Cheers,
Jelte

Reply | Threaded
Open this post in threaded view
|

Re: sorting and indices

Martin Engelschalk
Hi Jelte,

Jelte Liebrand schrieb:

>Hi there,
>
>Appologies if this is not the correct forum to send this mail to, but i
>thought I'd start here as you guys probably know how to use sqlite the
>best. If this is wrong, please let me know where i should be sending my
>mail to...
>
>I've recently created a little gaming server, which keeps track of
>online highscores. So far i've just used gdbm to keep track of the
>users, as all i really need is:
>- unique user cookie (to identify him)
>- user name
>- user score
>
>Howeveer, gdbm does not seem to be able to sort the records, so getting
>a 'current rank' withing the score list is tricky. So I'm thinking of
>changing this over to use sqlite.
>
>In doing so i've started to wonder how best to do this. The user cookie
>should be the primary key, but does sqlite support secondary keys?
>If not, should i just put an index on the score column?
>  
>
sqlite tables always have an integer Primary Key. If you do not define
one, sqlite adds one itself. This is also the rowid.
See http://www.sqlite.org/lang_createtable.html.

>Ideally i'd like inserted records to be inserted based on their score.
>That way i can just use the ROWID as their ranking. Otherwise i would
>need to do something like:
>
>select * from table order by score
>
>And then count somehow how far down the list the user is to work out his
>ranking. That sounds like a rather big performance hit, as i'd be
>listing and looping through _all_ users just to get the ranking for one.
>Surely there's a better way to use SQL to achieve this?
>  
>
If you have an Index on the score, you could do something like
select count(*) as rank from users where score < my_score

>I thought secondary keys could do just that, but then i'm not an SQL
>guru. So if anyone has any thoughts on how best to structure such a
>table, i'd be most grateful.
>
>(Note: every time the user updates his score i'd like to tell him what
>his current rank is. Hence looping through all records would be too
>costly i guess)
>
>/Cheers,
>Jelte
>  
>
Martin

Reply | Threaded
Open this post in threaded view
|

Re: sorting and indices

Jelte Liebrand
Hiya Martin,

> If you have an Index on the score, you could do something like
> select count(*) as rank from users where score < my_score

Thanks for your quick reply, that should work just fine!

/Cheers,
Jelte