How to make a column describing order of elements?

I want to add a column to a table that describes the order of the rows by other value. For example, i have the following data:

popularity = 1, id =1
popularity = 1, id =2
popularity = 2, id =3
popularity = 3, id =4
popularity = null, id=5

I want a query to get the following:

popularity = 1, id =1, newOrder = 4
popularity = 1, id =2, newOrder = 3
popularity = 2, id =3, newOrder = 2
popularity = 3, id =4, newOrder = 1
popularity = null, id=5, newOrder = 5

The rules that apply here is: – On same popularity, order by id – The highest popularity is the first on the new order – The null popularity is the lowest on the new order

Im trying to do this because the table has millions of rows, and doing a pagination by popularity and using offset is really slow. So having this new order will allow me to do something like id > page*page_amount.

It would be great to have an update query that sets this value because the select query with offset is really slow.

Thanks

Answer

I found the solution for this if anyone needs the same:

SET @actual_id := 0;
UPDATE file 
SET main_listing_page = (@actual_id:=@actual_id+1)
WHERE status = 1 And title_date is not null
ORDER BY popularity desc, id desc;

It uses a dynamic variable that changes in every step.

Advertisement

Answer