3 ms·

I am curious how fast you could query this data if it were inserted into a sqlite table with an index on hash.

genericlemon24 4y ago
I actually did try this, but I got impatient and stopped it after 30 minutes or so, and a ~20G database file. For some reason, after that, `ls` in that directory (or even shell completion) would freeze the shell entirely, even after reboot. I eventually managed to delete the file with `rm db.sqlite` (no completion allowed), but even that took like 2 minutes. I might try again with WAL enabled (based on my shell history, I also deleted a -journal file).
- 0b01 4y ago
  Have you tried DuckDB? It’s the columnar version of SQLite.
  - genericlemon24 4y ago
    Not yet, I might give it a try.
thewisenerd 4y ago
importing was sooo slow (something I likely messed up trying to make it go faster, was only chugging along at ~100MB/s) SQLite version 3.37.0 2021-12-09 01:34:53 here's the results tho sqlite> create table hashes (hash text, count text); sqlite> pragma cache_size=1000; sqlite> pragma synchronous=off; sqlite> .mode csv sqlite> .separator : sqlite> .timer on sqlite> .import pwned-passwords-sha1-ordered-by-hash-v8.txt hashes sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; ^CRun Time: real 14.841 user 9.370057 sys 2.363629 Error: stepping, interrupted (9) sqlite> create unique index hash_uniq on hashes(hash); Run Time: real 530.029 user 262.952507 sys 213.305437 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.004 user 0.000303 sys 0.000936 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.001 user 0.000310 sys 0.000306 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.000 user 0.000295 sys 0.000305 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.000 user 0.000320 sys 0.000279 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.001 user 0.000322 sys 0.000317 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.001 user 0.000128 sys 0.000155 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.001 user 0.000145 sys 0.000158

I actually did try this, but I got impatient and stopped it after 30 minutes or so, and a ~20G database file. For some reason, after that, `ls` in that directory (or even shell completion) would freeze the shell entirely, even after reboot. I eventually managed to delete the file with `rm db.sqlite` (no completion allowed), but even that took like 2 minutes. I might try again with WAL enabled (based on my shell history, I also deleted a -journal file).

0b01 4y ago
Have you tried DuckDB? It’s the columnar version of SQLite.
- genericlemon24 4y ago
  Not yet, I might give it a try.

thewisenerd 4y ago

importing was sooo slow (something I likely messed up trying to make it go faster, was only chugging along at ~100MB/s) SQLite version 3.37.0 2021-12-09 01:34:53 here's the results tho sqlite> create table hashes (hash text, count text); sqlite> pragma cache_size=1000; sqlite> pragma synchronous=off; sqlite> .mode csv sqlite> .separator : sqlite> .timer on sqlite> .import pwned-passwords-sha1-ordered-by-hash-v8.txt hashes sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; ^CRun Time: real 14.841 user 9.370057 sys 2.363629 Error: stepping, interrupted (9) sqlite> create unique index hash_uniq on hashes(hash); Run Time: real 530.029 user 262.952507 sys 213.305437 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.004 user 0.000303 sys 0.000936 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.001 user 0.000310 sys 0.000306 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.000 user 0.000295 sys 0.000305 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.000 user 0.000320 sys 0.000279 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.001 user 0.000322 sys 0.000317 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.001 user 0.000128 sys 0.000155 sqlite> select * from hashes where hash = 'F2B14F68EB995FACB3A1C35287B778D5BD785511'; F2B14F68EB995FACB3A1C35287B778D5BD785511:26437 Run Time: real 0.001 user 0.000145 sys 0.000158