in reply to Re: Key to use for files in db!
in thread Key to use for files in db!

Imho, your idea is good, but, why code it into perl when database engine is able to support it technically? If you use all features of the specific implementation of indexes, you can force the same effect and your sql queries will stay clear...
I know that this theme is off topic and I don't want flame, I only advice to observe and to use features of sql engine before modifying perl code.

Replies are listed 'Best First'.
Re^3: Key to use for files in db!
by Ace128 (Hermit) on Jan 05, 2006 at 22:53 UTC
    Em, how do you mean?
[reply]
      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]
        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]

In Section Seekers of Perl Wisdom