MySQL Forums
Forum List  »  General

Re: Record Position in Ordered Selection
Posted by: Peter Brawley
Date: June 08, 2009 12:10PM

>running 1,000 update queries every 15 minutes would be worse.
>Unless there's some other way to do it that I'm missing.

If you maintain a top1000 table(idcol int,rank smallint) ranked on tbl.valuecol keyed to tbl.idcol, and you decide it must be within x minutes of being current, then every x minutes you merely run

TRUNCATE top1000;
SET @i=0;
INSERT INTO top1000
SELECT idcol,@i:=@i+1 AS rank
FROM tbl
ORDER BY valuecol DESC LIMIT 1000;

which will be very fast, even if you block it in a transaction to prevent read-write collisions, and you get the rank of any idcol with a simple join.

PB
http://www.artfulsoftware.com

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.