In my never ending quest for speed ups I've been trying the following..
I pull in xml data for roughly (at the peak) 1.8M hosts from the BOINC
[EMAIL PROTECTED] hosts files.
Each host will have a unique id, a score, createdate and possibly a country
& team (as well as a number of other characteristics)
These have to be ranked in multiple ways.
A basic ranking is just by the score which I hold as a double, I index this
along with the id of the host computer.
A more complex ranking is for score within teams.
I use some sql as follows for this, fastest I've found to date
set @rank = 0,@pos = 0,@team:=null,@score:=null;";
update host_table set teamrank=
greatest( @rank:= if(@team = team and @score = rev_score, @rank,
if(@team <> team,1, @rank+1)),
least(0,@pos := if(@team = team, @pos+1,1)),
least(0,@team := team))
order by team,rev_score,id
Now note that the column is rev_score. Because mysql does not support
descending indexes, I added a column for which I subtract the score from
1,000,000,000 and use that as an index.
(score is unlikely to get above that anytime soon)
My question is, is this worth it? It certainly seems to be faster to me, but
not as much as I expected.
I did try originally subtracting from 0, but that caused the rankings to be
incorrect..
Regards
