Help with subquery

[sawwee]

I'm trying to create a custom stat which inludes a subquery.

My stat looks like:

SUM (CASE WHEN EXISTS
(SELECT 1 FROM
(SELECT (xxxxx) FROM tourney_hand_player_statistics thps_1 WHERE thps_1.id_player = 1 AND (xxxxx) ) as subtable WHERE subtable.xxxxxx AND subtable.id_hand = tourney_hand_player_statistics.id_hand)
THEN 1 ELSE 0 END)


(Sorry for deleting some part of my custom stats by xxxxxxx, frankly speaking I don't want to make my stat to be public.)

This stat is working perfectly until I want to get the result for the player who has id #1 in the database. But what should I do if I want to make this stat to work for any player (and put it to my HUD, for example?)
I tried to change ' WHERE thps_1.id_player = 1 ' to ' WHERE thps_1.id_player = tourney_hand_player_statistics.id_player ' , it seems to be giving a correct result, but running the quary takes a few HOURS.

What is the correct way to run a query including a subquery to ANY player?

Thanks a lot in advance!

[kraada]

You could exclude times where subtable.id_player is the same as tourney_hand_player_statistics.id_player - I don't think you care about that case, and it should make the query actually finish a bit sooner as it'll eliminate this case -- and besides, you'd really never want to do a query like this for a stat when the player is the subquery also you could just do it directly then.

[sawwee]

Thank you for your answer!

Frankly speaking I'm not perfectly understand what do you mean (especially not the last sentence you mention), however I tried to use this code instead of the original:

SUM (CASE WHEN EXISTS
(SELECT 1 FROM
(SELECT (xxxxx) FROM tourney_hand_player_statistics thps_1 WHERE thps_1.id_player = 1 AND (xxxxx) ) as subtable WHERE NOT (subtable.id_player = tourney_hand_player_statistics.id_player) AND subtable.xxxxxx AND subtable.id_hand = tourney_hand_player_statistics.id_hand)
THEN 1 ELSE 0 END)

It didn't really help, it takes still for hours, I definitely cannot use this stat in HUD.


What I actually need is to put the current player's id in the subquery. ("Where thps_1.id_player = ......" ) How can I do that? If I put any number there the query runs very fast and everything is nice. But how can I tell to PT4 to put the id of the current player there?

[WhiteRider]

I'm not sure exactly what you're trying to do, but if you want information about the current player then you don't need a subquery - a normal query will automatically apply to the current player.

[BillGatesIII]

Try

Code: Select all

WHERE thps_1.id_player = player_real.id_player

but I think the problem is in the double SELECT. You might need one less to speed things up. Trying to figure out what you want to accomplish

[sawwee]

I'm not sure exactly what you're trying to do, but if you want information about the current player then you don't need a subquery - a normal query will automatically apply to the current player.

Unfortunately I definitely need a subquery, becuase there are some parts in it which can't be in the normal query.

[sawwee]

I'm not sure exactly what you're trying to do, but if you want information about the current player then you don't need a subquery - a normal query will automatically apply to the current player.

Unfortunately I definitely need a subquery, becuase there are some parts in it which can't be in the normal query.

Thanks, I'll try it!

[sawwee]

I'm not sure exactly what you're trying to do, but if you want information about the current player then you don't need a subquery - a normal query will automatically apply to the current player.

Unfortunately I definitely need a subquery, becuase there are some parts in it which can't be in the normal query.

Thank you BillGatesIII, it is much faster now, but still too slow to be able to use it in the HUD.

Isn't there any way to get the active player's id from "outside" of the query?

[BillGatesIII]

player_real.id_player Is the active player from the outside query. It might be that the (xxxxx) part of your query does use non-indexed fields. If that's the case, it will be slow and you'll have to create indexes first.

[sawwee]

Hmmmmm.... But if I put the exact id instead of player_real.id_player (for example: thps_1.id_player = 1 ) then it works very quickly. What might be the reason?