3 ms·
I can confirm that indexing on a hashed value is definitely faster on SQLite at least. The reason is that indexing on a hash produces a much smaller index so m
by zffr 4y ago
I can confirm that indexing on a hashed value is definitely faster on SQLite at least.
The reason is that indexing on a hash produces a much smaller index so more of it can fit in memory. By minimizing disk seeks, you can speed up query time even if the Big O is the same. In both cases it should be O(log n) since SQLite uses btree indices.
- quickthrower2 4y agoSurely you give up LIKE queries benefiting from the index this way?
- necovek 4y agoLast time I needed this, LIKE queries still did sequential scans anyway, so the presence of an index or not did not matter. Also note that you can have multiple indexes in Postgres (well, any RDBMS). For emails in particular, you can even do partial indexes (eg. imagine filtering emails on @gmail.com to hit a separate index that's only half of your full table index). This requires some care with queries, but a wonderful feature regardless.
- tpetry 4y agoWith a trigram index in PostgreSQL you can even do index-supported leading wildcard searches.
- ComputerGuru 4y agoThis is purely up to the schema designer or dbadmin. You can create a Postgres index specifying “using hash” to specify that the index will not contain the contents of the field, just its hash. I’m pretty sure that still necessitates a hit to the db to prevent false hashes, but that’s going to be the case with your approach, too.