Help with subquery

[BillGatesIII]

Good point... What happens if you try another player id? Someone you have a lot of hands on.

[sawwee]

If I choose the id of the player I have the most hands (it's me actually), then the query runs in 1200 ms.
For the same player using "thsp_1.id_player = player_real.id_player" instead of the id it takes for ages, more than 1 million ms at the moment, and still running.

Using "thsp_1.id_player = player_real.id_player" we actually join thps_1 to player_real, don't we? I suppose the joined table will be huge, and that's why the query runs so slowly. What do you think?

[BillGatesIII]

No, the tables are not joined.

I created a column in roughly the same structure as yours and it's performing fine. Please note that the subquery here is not necessary to get this result, I just wanted to show you how it's built.

Code: Select all

sum(case when exists
         (select 1
          from (select thps.amt_p_effective_stack
                from tourney_hand_player_statistics thps
                where thps.id_player = player_real.id_player
                and thps.id_hand = tourney_hand_player_statistics.id_hand
                and thps.flg_p_3bet) as tt
          where tt.amt_p_effective_stack < 500)
          then 1 else 0 end)

[sawwee]

Thank you very much, I'll try it very soon!
I really appreciate your help!