in reply to Key to use for files in db!

If you want to access the rows via full path (you need this column in the table), and need not to reference this table from another one, you can use the full path as primary key. The effectivity of the index and proper column type depends on its implementation. Which database did you mean? PostgreSQL?

Replies are listed 'Best First'.
Re^2: Key to use for files in db!
by Ace128 (Hermit) on Dec 30, 2005 at 14:41 UTC
    Well, mySQL, SQLite, PostgreSQL.. you name it :)
[reply]
      No, I named PostgreSQL only :)
      Imho, in PostgreSQL, you should try to create the table with fullpath (columntype TEXT) as the primary key (which forces creating the most common type of index), fill it with real data (minimally 1000 rows), analyze the table and observe the optimizer compliancy to use it when typical queries happens. But, I personally have more conservative opinion, I typically create the integer column as primary key and the fullpath text column with NOT NULL UNIQUE constraint.
      And, after that, if you will not be satisfied, you can obtain more informations about performance tunning at
      http://www.postgresql.org/docs/
      or
      http://archives.postgresql.org/pgsql-performance/
      Good luck on this non-perl specific journey ;)
[reply]

In Section Seekers of Perl Wisdom