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

[Moshe Jacobson <moshe@xxxxxxxxxxxx>]

On Thu, Jan 24, 2013 at 4:57 AM, Alexander Farber <alexander.farber@xxxxxxxxx> 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@xxxxxxxxxxxx | www.neadwerx.com