The fastest way to create a lookup table in MySQL

Question

I want to create a lookup table for poker hand combinations. There are 113 million different possible hand combinations on a 7-card board.

If I give each card a number, say (1-52) and I want to save every possible combination in a table, what would be the best way to do this? I want it to be quick to search, so if I have a hand of 13,18,1,51,38,8,49, I can find a row in the table.

I could store each card in its own column like this:

poker_hands (id, card1, card2, card3, card4, card5, card6, card7)

or I could create some kind of hash value for 7 cards like:

$string= md5($card1 . $card2 . $card3 . $card4 . $card5 . $card6. $card7);

Then use this to find your hand

poker_hands (id, hash) 

(I will also store the ranking information for each hand in the database, but for now I just want to know how best to create a lookup table.)

Answer 1

1 52. :

create table numbers as
    select 1 as n union all select 2 union all . . .;

, :

create table numbers as
    select (@rn := @rn + 1) as n
    from t
    limit 52;

:

create table hands as
    select n1.n as card1, n2.n as card2, n3.n as card3, n4.n as card4,
           n5.n as card5, n6.n as card6, n7.n as card7
    from numbers n1 cross join
         numbers n2 cross join
         numbers n3 cross join
         numbers n4 cross join
         numbers n5 cross join
         numbers n6 cross join
         numbers n7;

, , .

EDIT:

, :

create table hands as
    select n1.n as card1, n2.n as card2, n3.n as card3, n4.n as card4,
           n5.n as card5, n6.n as card6, n7.n as card7
    from numbers n1 join
         numbers n2
         on n2.n not in (n1.n) join
         numbers n3
         on n3.n not in (n1.n, n2.n) join
         numbers n4
         on n4.n not in (n1.n, n2.n, n3.n) join
         numbers n5
         on n5.n not in (n1.n, n2.n, n3.n, n4.n) join
         numbers n6
         on n6.n not in (n1.n, n2.n, n3.n, n4.n, n5.n) join
         numbers n7
         on n7.n not in (n1.n, n2.n, n3.n, n4.n, n5.n, n6.n);