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

Question

Hello StackOverflow community:

The Situation

I am building an update query that sets ranks for all records in a table according to how each record compares to each other. Example of table:

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

So in this table, cost have the most weight in determining rank, followed by budget. Thus, record #2 will rank higher, while record #3 will be second and #1 will be last. As you can see, if two records have the same cost, then budget breaks the tie.

Now, In order to keep track of such 'weight' easily, I'm creating the rank_score column, which will hold the concatenation of cost and budget. Thus the rank_score for the table above would be:

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

This rank_score can be filled like:

UPDATE table_name
SET rank_score = CONCAT(cost, budget);

The Problem

Everything alright so far. But now comes the problem. I need an integer-only rank column for several stuff like sorting, but above all for showing the user the rank of his record. Of course, this rank column will be equal to the descending order of the rank_scores. But I cannot find a way to calculate this rank column in a single update query without having to do subqueries, loops in php, etc.

What I Have Tried

So, at first I was trying to fetch the rank_score calculation like:

SELECT id,
       CONCAT(cost, budget) AS rank_score
FROM table_name ;

and then looping in php all those rank_scores, only to build a query that went like:

UPDATE table_name
SET rank_score = CASE id WHEN 1 THEN 20500 END,
    rank = CASE id WHEN 1 THEN 3 END
WHERE id IN (1) ;

... Of course, this sample update query is not complete, as it has more WHEN THEN END clauses for each record in the table. Needless to say, this is ugly, especially when you expect to be having thousands and thousands of records.

So, in conclusion, I already have a way for calculating rank_score, but I also want to calculate rank (= descending order of rank score) in the same query, or at least without doing that crazy php looping and CASE WHEN THEN END clauses.

Thank You for Reading and Thinking About This ;)

Clarifications

Clarifying what @SJuan76 said: I cannot assign a rank via php since there are instances when the user will be shown a fixed quantity of records at a time (example, his user page: SELECT * WHERE user_id = 333, which could fetch 1, 3 or 8 records) and he needs to know what's the rank for each record. Assigning a rank via php in that case doesn't work because such rank will be relative to fetched records, not to all in table.

Answer 1

First, I would change budget, cost and rank_score into integer or other numeric datatype and instead of

UPDATE table_name
SET rank_score = CONCAT(cost, budget) ;

Then you would use:

UPDATE table_name
SET rank_score = cost * 1000 + budget * 1  ;

It's easier then as you won't have to deal with string functions and to have something like:

SELECT * 
FROM table_name
WHERE (conditions...)
ORDER BY rank_score DESC

---

(Parenthesis: having one parameter (1000) set so higher than the other (1) is equivalent to having an order of cost, budget. Try this to check:

SELECT * 
FROM table_name
ORDER BY cost DESC
       , budget DESC

So, you can well drop the rank_score alltogether, unless off course you plan to make experiments with various parameter values.

As others have pointed, it's not best practise to have a field that stores not data but a calculation. It's de-normalization. Instaed, you keep the table normalized and let the database do the calculations every time you need it:

SELECT id, budget, cost, 
       cost*1000 + budget*1 AS rank_score_calculated
FROM table_name
ORDER BY rank_score_calculated DESC

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

There is only one drawback. If the table is really big and you need that query (and the calculation) done by lots of users and very often, and the table is updated quite often, then it may slow your database. In that case, one should start thinking about adding such a field.

The other case is when one needs an absolute rank across all the table rows, like your need. Because MySQL has no "window" functions, it's very hard to write such a query in pure SQL.)

---

The rank can be calculated using MySQL variables

SELECT *
     , @rownum:=@rownum+1 AS rank_calculated
FROM table_name
   , (SELECT @rownum:=0) AS st
ORDER BY rank_score DESC

And if you want to put those values into rank, use:

UPDATE table_name
         JOIN
         ( SELECT id
                , @rownum:=@rownum+1 AS rank_calculated
           FROM table_name
              , (SELECT @rownum:=0) AS st
           ORDER BY rank_score DESC
         ) AS r
         ON r.id = table_name.id
SET table_name.rank = r.rank_calculated ;

The above two queries are not pure SQL. You may examine the option to move to another daabase system that supports window functions, like Postgres, SQL-Server or Oracle.

Answer 2

Have you tried splitting it up into two queries? Or using a subqery?

mysql> select p.*, (select count(0)+1 from table_name as s where s.cost >= p.cost and s.budget < p.budget) as rank from table_name as p where p.id in (1,2,3);
+----+------+--------+------+
| id | cost | budget | rank |
+----+------+--------+------+
|  1 |   20 |    500 |    3 |
|  2 |   40 |    400 |    2 |
|  3 |   40 |    300 |    1 |
+----+------+--------+------+
3 rows in set (0.00 sec)

Answer 3

SQL already can order the records as you wish, without need of additional tablespace and (much more important) without breaking normal forms.

And the rank can be obtained directly from the ordering, just ask the ordering you want and when you retrieve the data add the index from its order, in the program code.

Why do you want to do this in the database?