Справка MySQL: оптимизация запроса на обновление, который устанавливает ранг в соответствии с порядком другого столбца

Во-первых, я бы изменил budget, cost и rank_score на целое число или другой числовой тип данных и вместо

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

Затем вы должны использовать:

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

Тогда это проще, так как вам не придется иметь дело со строковыми функциями и иметь что-то вроде:

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

(Скобка: установка одного параметра (1000) выше другого (1) эквивалентна порядку cost, budget. Попробуйте это, чтобы проверить:

SELECT * 
FROM table_name
ORDER BY cost DESC
       , budget DESC

Так что вполне можно вообще отказаться от rank_score, если, конечно, вы не планируете проводить эксперименты с различными значениями параметров.

Как указывали другие, не рекомендуется иметь поле, в котором хранятся не данные, а расчет. Это денормализация. Вместо этого вы нормализуете таблицу и позволяете базе данных выполнять вычисления каждый раз, когда вам это нужно:

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

rank_score_calculated не сохраняется в приведенном выше примере. Таким образом, вам не придется обновлять вычисляемое поле каждый раз, когда изменяется бюджет или стоимость или добавляется новая строка в таблицу.

Есть только один недостаток. Если таблица действительно большая и вам нужно, чтобы этот запрос (и расчет) выполнялся большим количеством пользователей и очень часто, а таблица обновлялась довольно часто, это может замедлить работу вашей базы данных. В таком случае стоит задуматься о добавлении такого поля.

Другой случай - это когда нужно абсолютное rank во всех строках таблицы, как вам нужно. Поскольку в MySQL нет "оконных" функций, очень сложно написать такой запрос на чистом SQL.)

Рейтинг можно рассчитать с помощью переменных MySQL.

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

И если вы хотите поместить эти значения в rank, используйте:

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 ;

Приведенные выше два запроса не являются чистым SQL. Вы можете изучить возможность перехода на другую систему баз данных, которая поддерживает оконные функции, такие как Postgres, SQL-Server или Oracle.

Вы пробовали разделить его на два запроса? Или с помощью подзапроса?

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)

SQL уже может упорядочивать записи по вашему желанию, не требуя дополнительного табличного пространства и (что гораздо важнее) не нарушая нормальные формы.

И ранг можно получить непосредственно из порядка, просто задайте порядок, который вы хотите, и когда вы получите данные, добавьте индекс из его порядка в программный код.

Почему вы хотите сделать это в базе данных?