MYSQL/PHP: Comparing large sets of 2 specific rows in one table (POKER-calculator)

Question

I am writing an online poker calculator just for fun :) I tried the pure php calculation approach, to compare two hands it calculates the outcome for every possible deck (C(5,48) = 1712304 decks) That takes around 12 seconds on my sucky one.com server :D What is ofcourse far too slow if i would ever put it online for public. So I tried a new approach, databases, I stored all the combinations for 7 cards (hand + deck) in a database. So I have a database of 5gb over 130mil rows with a primary key (the deck representation in binary) and the points or rank of those 7 cards.

So lets say the columns are called a and b, where a is the primary key.

I now want/need to compare the b where (a = x) and (a = y) but again in the worst case for : C(5,48).

So for example in badly written code :

$ar = array(array(1,4),array(53,422),array(4423423,472323),array(71313,13131));

for ($i = 0; $i < count($ar);$i++)
{
    $value_one = mysql_fetch_assoc(mysql_query('select `b` from `hugetable` where (`a` = ' . $ar[$i][0] ' . LIMIT 1;'))['b'];
    $value_two = mysql_fetch_assoc(mysql_query('select `b` from `hugetable` where (`a` = ' . $ar[$i][1] ' . LIMIT 1;'))['b'];
    if ($value_one > $value_two)
        $win++;
    elseif ($value_one < $value_two)
        $lose++;
    else
        $draw++;
}

So the question is, is there a faster way ? Also is there a direct way to do this and get a table win win draw loss back immediately?

All help and answers are welcome!!! :)

EDIT: This approach clearly didnt work out very good haha :D it took around 100 seconds :D

Any other ideas are welcome !

Answer 1

One way that is worth trying is to let the database do most of the work. Transfer your array to a temporary table with the primary keys of the matches to compare:

create temporary table match_list (int pk1, int pk2);

Now you can query the bigger table for the win/loss/draw statistics:

select  sum(case when t1.score > t2.score then 1 end) as wins
,       sum(case when t1.score < t2.score then 1 end) as losses
,       sum(case when t1.score = t2.score then 1 end) as draws
from    match_list
join    match_results t1 force index (pk_match_results)
on      t1.pk = match_list.pk1
join    match_results t2 force index (pk_match_results)
on      t2.pk = match_list.pk2

I've added the force index hint which might help for a relatively small number of lookups to a very large table. You can find the name for an index using show index from mytable.