row number in result...

Anon

I run an online game with php and mysql, and users want their current rank on the account page...

now to do that, I order the results and do a bunch of

mysql_fetch_array($result)

until i find their id. this seems like a HUGE cpu hogging way to do this.

is there a way to do the query with ordered results, and then get the row number for a certain user?

i actually have another question, i was just going to dump the results in a new table that would have an auto_increment rank field, and then just delete all rows from that table, and rank the users every 15 minutes... the problem is, even if the table is completely cleared, the auto_increment never goes back to 0!

any help would be GREAT!

brandonschnell

kinda a pain as mysql doesn't support "select into" or subselects

the cheesy way would be to have two tables. one with all of your unsorted data with user & points. with another table, drop and rebuild a sorted table every 15 minutes by selecting "order by points desc" into an outfile then importing back into the new table the data from the unsorted table. have your queries check to be sure the db is available ("please wait while we tabulate by hand your incredibly high score") and get the users current rank from the newly sorted table id.

in other words insert and delete user data from the unsorted table and give users ranking info from the sorted table.

Anon

What you probably want to do is math instead of a selection with this. For instance, lets say you first get the score with :

create table scores (score int, id primar...);

scores: (in no particular order)

score | id

100___1
95_3
75_6
92_4
88____2

Get the score of our user:
select score from scores where user_id=$user_id;

Find out what place we're in, it's this plus 1:
select count(*) from scores where score>$our_score;

Get the people from 1st through 10th:
select * from scores order by score limit 10;

Get all the people who outscored us:
select * from scores where score>$our_score

And we can tell if he's tied for his place, anything over one is a tie. I.e. we're tied with this minus one:
select count(*) from scores where score=$our_score;

And so on.

Let the database it, it scales very well. I.e. it's better to have the heavy lifting happen in the database, because often selecting a huge set like with a

Just avoid things without limits and such, like:

select * from scores;

Anon

will that still work ok (server load wise...) if there were more than 20,000 users?

i am reffering to the count(*) where value<$my_value to get the rank...

[deleted]

MySQL does let you select into a table:

http://www.mysql.com/doc/M/i/Missing_SELECT_INTO_TABLE.html

Anon

yeah, i already knew that, and that is how I was going to do it, but this seems better.

Anon

But performance wise, selecting into a table is going to be expensive compared to a select count(*) where yada/limt yada is going to be, at least on most databases. Not sure about mysql.

Anon

As long as your table is indexed, yes. Otherwise what's really gonna cost you is the sort when you stick an order by on the end.

the limit 10 or limit 20 will keep the database from working real hard.

You want to keep the result set as small as possible, and transfer as little data as possible, and indexing and limiting should do that.

Here's some info:

I build a 100,000 row table with one word and one int.

Here's the output from a quick test:

postgres=# explain select * from test order by id limit 10;
NOTICE: QUERY PLAN:

Limit (cost=0.00..0.59 rows=10 width=28)

-> Index Scan using test_id_key on test (cost=0.00..59.00 rows=1000 width=28)

Note that the limit costs a max of 0.59, while the index scan costs anywhere from 0.00 to 59.00 depending on the internal ordering of the dataset. This query runs in <1 second.

Without an index, we get:

postgres=# explain select * from test2 order by id limit 10;
NOTICE: QUERY PLAN:

Limit (cost=11057.75..11057.75 rows=10 width=16)
-> Sort (cost=11057.75..11057.75 rows=100000 width=16)

-> Seq Scan on test2 (cost=0.00..1658.00 rows=100000 width=16)

Now look at the cost of the limit and sort and seq scan. This query takes 8 seconds on my computer.

[deleted]

Just pointing out that mysql does let you select into a table, that's all! :-)