Re: Optimizing select count query which often takes over 10 seconds

On Thu, Jan 24, 2013 at 4:57 AM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)com> wrote:

> 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
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com