Phil schrieb:
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.
index(id, rank) is useless, cause id should have already an index, used when
querieng for specific id
but if you query for rank, or order by rank, this index is not used, because
rank needs to come first to be used
index(id, rank) will only be usefull if you do something like
SELECT ... WHERE `id` IN(1,2,3,...) ORDER BY `rank`
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
possiblke some sort of multi table update, which includes your team table
could be faster, and less complex, but i am not sure without knowing your
whole schema (structure of db, tables)
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)
how about just negate the score (score * -1)? 1.234 becomes -1.234 ?
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..
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
