getting position

mfolnovich · 06-27-2008, 10:31 PM

So, I have mysql table users:
ID name score

and when getting position of user 'abc', I use:

Code:

$q = select * from users order by score; 
$o = mysql_query($q);
for($i=0;$r=@mysql_fetch_assoc($o);++$i)
    if ($r["name"] == $username) return true;

my question is how can I optimize it, I might have thousands of users ...

Salathe · 06-28-2008, 12:47 AM

You might go for something within MySQL rather than looping through every result until you find the right one in PHP.

SQL Code:

SELECT 
    ID, name, score,
    (
        SELECT COUNT(ID) + 1 
        FROM users AS i 
        WHERE i.score > o.score
    ) AS rank
FROM users AS o
WHERE name = 'abc'
ORDER BY score DESC

I did a quick test with 100,000 users and it seemed to work ok but would advise really examining the whole approach and see what works best for large data sets (PHP vs MySQL, different queries, etc.).

Apologies for any mistakes (or really bad SQL form!), it's 2am and I've just finished packing for my trip this evening... my mind's on other things, time to snooze for a while.

mfolnovich · 06-28-2008, 09:45 AM

Ok, tnx, I'm going to wedding soon, so I'll test it when I get back, but when I just look at it, seems fine ...

EDIT: another question, if I have table messages:
ID name content read datetime
1 foo fooo ,1,2 12
2 foo2 fooo2 ,2 19
3 foo3 fooo3 ,3,1 16
4 foo4 fooo4 , 21

current userID is 1, when I sort it, I want to have:
ID name content read datetime
2 foo2 fooo2 ,2 19
4 foo4 fooo4 , 21
1 foo fooo ,1,2 12
3 foo3 fooo3 ,3,1 16

so first goes all messages that are not read (all which doesn't have current userID in `read`), and those are sorted with datetime ... how would query be ?

btw. sorry for my english ...