select count(id) from ( select id, row_number() over(partition by yw order by money desc) as ranking from pref_money ) x where x.ranking = 1 and id='OK471018960997'
Does anybody please have an idea how could I optimize it or should I introduce a hourly job and a "medals" column (that would make my players stats less "live")?
One idea is to have a new ranking column to cache every player's ranking for every weekly tournament. However, instead of updating it hourly with a cron job, you could have a trigger on the table, such that when any row is updated/inserted, you recalculate the rankings for only those rows having the same "yw" value.
Then, you might want to create an index on the ranking column as well as the yw column, which you already have indexed
Moshe
--
Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer