Re^3: Key to use for files in db!

Replies are listed 'Best First'.
Re^4: Key to use for files in db! by pajout (Curate) on Jan 06, 2006 at 11:12 UTC
I see following solutions in PostgreSQL: 1/ supposing btree unique index on path column `SELECT * FROM mytable WHERE path = '/my/path/to/file/isthis.dat';` [download] I guess that it could be OK when database finely tuned. But optimizer could decide that index is not selective. 2/ supposing hash unique index on path column - the same as previous but index will be selective, i hope. This index type is not very recommended. 3/ supposing (functional) unique btree index on md5(path): `SELECT * FROM mytable WHERE md5(path) = md5('/my/path/to/file/isthis.dat');` [download]	[reply] [d/l] [select]
Re^5: Key to use for files in db! by Ace128 (Hermit) on Jan 17, 2006 at 19:17 UTC
Hmm, kewl. Now, the question is, what is the BEST (fastest) way? #1 or #3? In this case #1 seems better, since it doesnt have to calculate the md5 twice. And maybe its better to have a column with that already calculated, and not calculate twice.	[reply]
Re^6: Key to use for files in db! by pajout (Curate) on Jan 18, 2006 at 18:23 UTC
Why do you think that the fastest means the best? :>))) But I am afraid of your problem is not fully resolvable without some experiments. Ad #3 - the md5 hash is (re)calculated when the _index_ built or modified, so the special column is not necessary. And, when the query runs, only md5 of queried string is calculated.	[reply]