ansaurus

Question

Answer 1

+2 A:

set @rank = 0;
update tbl a join (select score, @rank:=@rank+1 as rank from tbl group by score
  order by score desc) b on a.score = b.score set a.position = b.rank;

to update the position in one fell swoop that would do the trick. equal scores get equal position

2010-02-06 17:20:44

Ah, thanks that looks like it and `rank` was the keyword I should have used earlier!

Pool 2010-02-06 17:34:25

Answer 2

+1 A:

Calculate it in the language which you are using. Thus your solution will be:

more portable
more readable
equivalently efficient

Bozho 2010-02-06 17:20:49

Thanks, but I'm looking for a solution similar to this: http://stackoverflow.com/questions/1600294/mysql-add-sequence-column-based-on-another-field

Pool 2010-02-06 17:23:54

I was trying to hint why such a solution is not that preferable :)

Bozho 2010-02-06 17:52:36

Answer 3

+1 A:

SELECT * FROM your_table ORDER BY score;

Your position column appears to be redundant, although it is hard to tell from the information given in the question. The functionality you appear to want is accomplished using database row ordering, seen in the above example with the 'ORDER BY' expression. Even if you would want a position column for some good reason, remember that most likely there exists an index for the score column anyway, which would in most cases be doing exactly the same thing that a position column would do. Either that, or I completely misunderstand your question.

amn 2010-02-06 17:23:23

Thanks amn, yes, position is a redundant but I need to use it for optimisation. Position column would be a cached value of position, created infrequently. (It is very expensive to sort each time I wish to get the position, being set every 5 minutes for example is fine.)

Pool 2010-02-06 17:24:45

The optimization is much better done by the database system itself, when you explicitly tell it to index the score column using say the following SQL expression: CREATE INDEX score_index ON your_table(score);

amn 2010-02-06 17:27:34

I already do have the score as an index.

Pool 2010-02-06 17:29:54

If you have a score as an index, you CANNOT POSSIBLY optimize your ordered score retrieval performance, and I am willing to bet on my statement. Try to benchmark your query for a table with a position column versus the query I have written above - I am betting you will gain nothing from a table with a position. Or, again, I do not understand what you actually want with the table.

amn 2010-02-06 17:32:19

I've found a page which shows what I mean. They recorded a 100* performance gain using rank columns http://oreilly.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html?page=6

Pool 2010-02-06 18:02:27

Might take me a while to get my head around the information presented in the article, but thanks.

amn 2010-02-06 18:20:50

Yes - the justification for taking the action was quite convoluted so I simplified and just detailed the part I was stuck on. It's good to question the motivations though.

Pool 2010-02-06 18:58:52

ansaurus

tags:

views:

answers:

MySQL: adding a position column

related questions