i run a site that collects hand history data from a real money online poker site. one of my reports is the best/worst players at each table limit for texas hold'em.
for this i will be using 3 tables. here are the fields that matter.
player
player_id INT
name varchar(64);
hand
hand_id INT
table_limit enum('1-2','2-4','3-6','5-10','10-20','25-50')
player_hand
player_id INT
hand_id INT
net_change decimal(5,2)
I run it once for each table limit and it takes forever as there are over 5,500,000 records in player_hand, over 10,000 records in player and over 900,000 records in hand.
What i would like is a single query that would give me the top 10 best players by each limit that runs in under 5-10 seconds. to calculate who is the best i need to know the total amount they have lost and the total number of hands they have played in at that limit. so if i played 1000 hands at table_limit 1-2 and have a SUM of my net_change at $1,000... that means i make $1 a hand, and that is what it should sort on.
ANY ADVICE WOULD BE GREATLY APPRECIATED.
Here is the code i currently use to get the 10 worst at a given $table_limit (in this case '1-2')
SELECT
player.player_id,
player.name,
SUM(player_hand.net_change) as 'total_net',
SUM(1) as 'total_hands',
(SUM(player_hand.net_change) / SUM(1)) as 'per_hand'
FROM player, hand, player_hand
WHERE
player.player_id = player_hand.player_id
AND hand.hand_id = player_hand.hand_id
AND hand.table_limit='1-2'
GROUP BY player.player_id
ORDER BY per_hand ASC
this code works but takes far too long to execute. I also put a restriction that i only want the player to count if they have played more than 250 hands at that limit... so for now i don't have a limit and loop through the result set until i find 10 players that have over 250 total_hands.
i need an SQL guru to help me out here.
