MySQL Help: Optimize Update Query that Sets Rank According to Order of Another Column -

hello stackoverflow community:

the situation

i building update query sets ranks records in table according how each record compares each other. example of table:

id | budget | cost | rank | rank_score    1 |   500  |  20  |   ?  |     ?    2 |   400  |  40  |   ?  |     ?    3 |   300  |  40  |   ?  |     ? 

so in table, cost have weight in determining rank, followed budget. thus, record #2 rank higher, while record #3 second , #1 last. can see, if 2 records have same cost, budget breaks tie.

now, in order keep track of such 'weight' easily, i'm creating rank_score column, hold concatenation of cost , budget. rank_score table above be:

id | budget | cost | rank | rank_score    1 |   500  |  20  |   ?  |   20500     2 |   400  |  40  |   ?  |   40400    3 |   300  |  40  |   ?  |   40300   

this rank_score can filled like:

update table_name set rank_score = concat(cost, budget); 

the problem

everything alright far. comes problem. need integer-only rank column several stuff sorting, above showing user rank of record. of course, this rank column equal descending order of rank_scores. cannot find way calculate rank column in single update query without having subqueries, loops in php, etc.

what have tried

so, @ first trying fetch rank_score calculation like:

select id,        concat(cost, budget) rank_score table_name ; 

and looping in php rank_scores, build query went like:

update table_name set rank_score = case id when 1 20500 end,     rank = case id when 1 3 end id in (1) ; 

... of course, sample update query not complete, has more when end clauses each record in table. needless say, ugly, when expect having thousands , thousands of records.

so, in conclusion, have way calculating rank_score, want calculate rank (= descending order of rank score) in same query, or @ least without doing crazy php looping , case when end clauses.

thank reading , thinking ;)

clarifications

clarifying @sjuan76 said: cannot assign rank via php since there instances when user shown fixed quantity of records @ time (example, user page: select * user_id = 333, fetch 1, 3 or 8 records) , needs know what's rank each record. assigning rank via php in case doesn't work because such rank relative fetched records, not in table.

first, change budget, cost , rank_score integer or other numeric datatype , instead of

update table_name set rank_score = concat(cost, budget) ; 

then use:

update table_name set rank_score = cost * 1000 + budget * 1  ; 

it's easier won't have deal string functions , have like:

select *  table_name (conditions...) order rank_score desc 

---

(parenthesis: having 1 parameter (1000) set higher other (1) equivalent having order of cost, budget. try check:

select *  table_name order cost desc        , budget desc 

so, can drop rank_score alltogether, unless off course plan make experiments various parameter values.

as others have pointed, it's not best practise have field stores not data calculation. it's de-normalization. instaed, keep table normalized , let database calculations every time need it:

select id, budget, cost,         cost*1000 + budget*1 rank_score_calculated table_name order rank_score_calculated desc 

rank_score_calculated not stored in above example. way, won't have update calculated field every time budget or cost changed or new row added in table.

there 1 drawback. if table big , need query (and calculation) done lots of users , often, , table updated quite often, may slow database. in case, 1 should start thinking adding such field.

the other case when 1 needs absolute rank across table rows, need. because mysql has no "window" functions, it's hard write such query in pure sql.)

---

the rank can calculated using mysql variables

select *      , @rownum:=@rownum+1 rank_calculated table_name    , (select @rownum:=0) st order rank_score desc 

and if want put values rank, use:

update table_name          join          ( select id                 , @rownum:=@rownum+1 rank_calculated            table_name               , (select @rownum:=0) st            order rank_score desc          ) r          on r.id = table_name.id set table_name.rank = r.rank_calculated ; 

the above 2 queries not pure sql. may examine option move daabase system supports window functions, postgres, sql-server or oracle.