5 ms·

These days you can simply load it into a database (ClickHouse) - it will be compressed (about the same as the 7z file), and the search will take 3 ms: https://

• intelVISA 4y ago
  Lots of great solutions in the in-memory columnar data space, what a time to be alive.
  • winrid 4y ago
    Clickhouse is not in memory.
    • gkbrk 4y ago
      ClickHouse has a table engine for keeping data in memory. Can be accessed like this create table tableName (.....) Engine = Memory();
      • zX41ZdbW 4y ago
        But it's not necessary to keep data in Memory, because MergeTree works perfectly. Here is a slide deck showing why MergeTree is faster than in-memory tables: https://presentations.clickhouse.com/meetup53/optimizations/ https://presentations.clickhouse.com/meetup53/optimizations/
        • ilyt 4y ago
          Soooo did they just implement in memory store worse ?
          • winrid 4y ago
            It's a database
        • ww520 4y ago
          The comparison is done with the in-memory table doing full scan while MergeTree using an index. Kind of meaningless comparison.
• 0b01 4y ago
  DuckDB is also great for stuff like this. You can replace a MapReduce cluster with a single SQL.
  • zX41ZdbW 4y ago
    I checked DuckDB and your statement appears to be untrue. >>> con.execute("CREATE TABLE passwords (hash TEXT, count INT)") <duckdb.DuckDBPyConnection object at 0x7fc7bceb55f0> >>> con.execute("CREATE INDEX ix_hash ON passwords (hash)") <duckdb.DuckDBPyConnection object at 0x7fc7bceb55f0> >>> con.execute("COPY passwords FROM 'pwned-passwords-sha1-ordered-by-hash-v8.txt' (SEPARATOR ':')") 100% 100% It froze in an attempt to load the data. Nothing happens after it displays 100%.
    • zX41ZdbW 4y ago
      I cannot even ssh into the server after trying to use DuckDB. It is completely dead (with all the ducks, what a misery). The reason is probably that it's using a full index, in contrast with the sparse index in ClickHouse, and maybe it's trying to build it in memory, going to swap (the server has 32 GB memory).
      • zX41ZdbW 4y ago
        Interesting - the database file looks ok, but the data is lost (the table is empty): ubuntu@ip-172-31-3-138:~$ ls -l total 69561648 -rw-rw-r-- 1 ubuntu ubuntu 17631031296 Dec 16 23:57 my-db.duckdb -rw-rw-r-- 1 ubuntu ubuntu 326 Dec 16 23:53 my-db.duckdb.wal -rw-rw-r-- 1 ubuntu ubuntu 16257755606 Jan 21 2022 pwned-passwords-sha1-ordered-by-hash-v8.7z -rw-rw-r-- 1 ubuntu ubuntu 37342268646 Dec 2 2021 pwned-passwords-sha1-ordered-by-hash-v8.txt ubuntu@ip-172-31-3-138:~$ python3 Python 3.10.6 (main, Nov 14 2022, 16:10:14) [GCC 11.3.0] on linux Type "help", "copyright", "credits" or "license" for more information. >>> import duckdb >>> con = duckdb.connect(database='my-db.duckdb') >>> con.execute("SELECT count(*) FROM passwords").fetchall() [(0,)]
        • ignoramous 4y ago
• hedora 4y ago
  “These days”? A 32 bit intel running mysql back in 2000 would probably have laughed at this query. Granted, I’d expect one or two disk seeks, at ~ 10 ms each. I imagine on modern hardware, it would be in the 100’s of usec range. (Assuming you limited it to 2-4 GB of ram).
• Aeolun 4y ago
  But the point was to make it take less than 1ms. How would 3ms help?