Best practices - in memory database table

I often need to load an entire database table into an in-memory perl
variable, for use in quick data lookups. So, for example, I may store
a database table with many columns into a 2 dimensional hash named
%data, using the table's primary key ("id") to index one of the hash
dimensions:

$data{$hashref->{id}} = $hashref;

This is fine and good, I can use this %data hash in my program to
access the data stored in the table. The lookups are quick when
accessing my primary key lookup. However, there may be other UNIQUEly
constrained columns in the table, let's use "filename" as an example.
I cannot efficiently access the record in %data that corresponds to a
particular "filename", I would have to look through every "id" in %data
until I found the matching "filename".

So, at the expense of doubling my memory requirements, I could
construct the hash thusly:

$data{'by_id'}{$hashref->{id} = $hashref;
$data{'by_filename'}{$hashref->{filename}} = $hashref;

Now I can do quick lookups on the records stored in %data using either
"id" or "filename".

Obviously, this solution is not a general solution for any particular
column in the table. My question is, what do people consider the most
flexible design for the perl hash variable to allow for a general way
to perform quick hash-like lookups of data?

I cannot imagine that my solution (expanding the hash for each column
to be indexed) is the best solution.

Thanks for any pointers, Jon

.

Relevant Pages

  • Re: Comment on how to uniquely track your objects in C# / hash table / get hash code
    ... Array, correct? ... This is largely irrelevant to the issue of performance, since hash ... where both insertions and lookups happen frequently, ... about fast lookups for balanced red/black binary trees. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: A use case for an ordered hash
    ... I never use a hash for fast lookup. ... So you're saying that you wouldn't bother if lookups were O? ... def to_assoc ...
    (comp.lang.ruby)
  • Re: A use case for an ordered hash
    ... On 8/14/06, Hal Fulton wrote: ... >> I never use a hash for fast lookup. ... So you're saying that you wouldn't bother if lookups were O? ...
    (comp.lang.ruby)
  • Re: I dont understand this hash table calculation
    ... "However, if each element hashes to the same bucket, the hash table ... I thought it took constant time ) to search a hash table and ... Insertion of one element into a linked list, in order, takes O. ... lookups, it makes sense to choose the in-order solution... ...
    (comp.programming)
  • Re: symbol tables and search tries
    ... Hash headers need only be one word ... point, when computing the complexity order, N is not limited. ... This could be, an email I got suggests, that a compiler ... perhaps uses a lot of lookups, ...
    (comp.compilers)