Open Chess Game Database Standard

[phhnguyen]

Probably a sort of table sharding will also speedup querying. Postgres IIRC has partition by on relations, one can use that to make your queries faster

https://www.postgresql.org/docs/10/ddl- ... oning.html

AFAIK, SQLite uses the same syntax as PostgreSQL but they are still different with different ways to use. I don't know (yet) how to apply that tech.

[amanjpro]

Probably a sort of table sharding will also speedup querying. Postgres IIRC has partition by on relations, one can use that to make your queries faster

https://www.postgresql.org/docs/10/ddl- ... oning.html

AFAIK, SQLite uses the same syntax as PostgreSQL but they are still different with different ways to use. I don't know (yet) how to apply that tech.

I am not sure if SQLite has a built-in feature for partitioning. But one can always do it manually. Say create a parent table that maps hashes that are between 0 to N-1, to table 1. N to 2N-1 to table 2 and etc.

Your query might become two level, but you still might gain a lot of speed on average

[phhnguyen]

Probably a sort of table sharding will also speedup querying. Postgres IIRC has partition by on relations, one can use that to make your queries faster

https://www.postgresql.org/docs/10/ddl- ... oning.html

AFAIK, SQLite uses the same syntax as PostgreSQL but they are still different with different ways to use. I don't know (yet) how to apply that tech.

I am not sure if SQLite has a built-in feature for partitioning. But one can always do it manually. Say create a parent table that maps hashes that are between 0 to N-1, to table 1. N to 2N-1 to table 2 and etc.

Your query might become two level, but you still might gain a lot of speed on average

Oh, I see. Good idea. Will try. Thanks.

[dangi12012]

Probably a sort of table sharding will also speedup querying. Postgres IIRC has partition by on relations, one can use that to make your queries faster

https://www.postgresql.org/docs/10/ddl- ... oning.html

AFAIK, SQLite uses the same syntax as PostgreSQL but they are still different with different ways to use. I don't know (yet) how to apply that tech.

I am not sure if SQLite has a built-in feature for partitioning. But one can always do it manually. Say create a parent table that maps hashes that are between 0 to N-1, to table 1. N to 2N-1 to table 2 and etc.

Your query might become two level, but you still might gain a lot of speed on average

Oh, I see. Good idea. Will try. Thanks.

This wont help. The problem is just that the position X is there 1E6 Times splitting wont help and is against sql fundamentals. Keep it in one table. We have to live with that i guess because you can see that 50ms is acceptable after ply 5

[dangi12012]

Again the problem here is not that the hashes are not good but that we have so many duplicates like
01231
01231
01231
etc.. for 1e6 times.

If we limit to a single player or a group of players this problem goes away.

[Sopel]

who even needs millions of results, just slap LIMIT 100 on it and be done

[amanjpro]

who even needs millions of results, just slap LIMIT 100 on it and be done

Probably pagination instead of limit. But yeah limiting the resultset is the way to go

[Dann Corbit]

Here are some fide games with the hash:


Some issues:
We do not need the Elo in both the header and the player table, that is redundant.
The hash is interpreted as a 4 byte integer by the SQLite ODBC driver, even though it is stored correctly. (I added an index on the hash).
It might help as a hint to the ODBC driver if the type of the hash were changed to:
BIGINT or INT8 or (better yet) UNSIGNED BIG INT

[Dann Corbit]

It would be nice to have a user defined function that can make the hash from a FEN/EPD.
That way we could do simple lookups like
SELECT gameid from hash where hash = makehash('rnbqkbnr/pppppppp/8/8/1P6/8/P1PPPPPP/RNBQKBNR b KQkq -')

[dangi12012]

It would be nice to have a user defined function that can make the hash from a FEN/EPD.
That way we could do simple lookups like
SELECT gameid from hash where hash = makehash('rnbqkbnr/pppppppp/8/8/1P6/8/P1PPPPPP/RNBQKBNR b KQkq -')

I can write you one this evening. will update you! - I need the Table to exist which the HashCode is calculated from

Code: Select all

create table Zobrist(piece char, square integer, hash integer,
   primary key (piece , square)
);