Fastest way to create a lookup table in MySQL

Question

I want to create a lookup table for the combinations of poker hands. There are 113million different possible hand combinations in a 7 card board.

If I give each card a number say (1-52) and want to store each possible combination in the table, what would be the best way to go about doing it? I want it to be fast to lookup, so that if I have a hand 13,18,1,51,38,8,49 I can search for the row in the table.

I could store each card in it's own column like so:

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

or I could perhaps create some sort of hash value for the 7 cards like:

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

Then use that to lookup the hand

poker_hands (id, hash)

(I'll be storing information about the rank of each hand in the database too; but for now I just want to know the best way of creating a lookup table.)

The hash seems good. But if you generate it, check, is there any duplicate. I do not know about performance with 113million record, I think it should not be problem, but if you stuck with it, you can try a nosql. — vaso123, Oct 02 '15 at 10:45
Use a [stored procedure](https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html) to store your pairs in a static structure. Then you can access it fairly quickly. — hd1, Oct 02 '15 at 10:50
Why don't use as key the seven card ids together (with format %02d, to avoid errors like 1,2=12) from lower to highervalue? So, for example, for combination cards 2, 43, 25, 31, 1, 10 and 11, the id would be 01021011253143 — Amarnasan, Oct 02 '15 at 11:11
@Amarnasan A hash effectively does the same thing, but is more efficient — Strawberry, Oct 02 '15 at 12:55
Why use a database? The 2+2 evaluator stores the whole 7-card table in a compact form in memory in about 200MB, which most computers these days can handle. My 5-card evaluator uses less than 1MB for the table, and has an optimized 7-card lookup that's not quite as fast as 2+2's, but certainly a lot faster than a database lookup. — Lee Daniel Crocker, Oct 02 '15 at 16:22
Not sure if a database is your best option. The latency of the database transaction will create overhead that will likely be 1000 times slower than if using a local memory LUT. Decent hand evaluators can process 50+m hands per sec on desktop PCs... that would never be possible using MySQL — JSON, Nov 01 '15 at 08:48

Gordon Linoff · Answer 1 · 2015-10-03T02:44:17.510

Start by creating a list of numbers from 1 to 52. You can do this as:

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

Or, from a table that already exists:

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

Then, create the combinations using a cross join:

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;

This is probably the fastest approach within the database.

EDIT:

If the cards should be different, then put in the appropriate conditions:

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);

This would also have combinations like 1, 1, 1, 1, 1, 1, 1 which are not valid in the current context. — Verma, Oct 02 '15 at 11:18

Fastest way to create a lookup table in MySQL

1 Answers1