Mike,
Thank you very much for your useful reply.
the way to do this is to use a second db instance, open it to :MEMORY:, and
copy the data (once) to this new db. then, you will have a RAM-based db,
which should be fast.
Yes, this is what I was hoping to do with the first method. Thanks - I'll give this a try.
I am not sure I understand your GUI topology, though. dirlling down is a
process of elimination, as I understand it, which means executing queries
against progressively smaller result sets (temporary tables would do fine
here), and should be very fast with proper indexes. once I select
"vacation", I have a list of vacation photos. What is the user's next action
? do they select "sports" ? If so, is this meant to be "select photos with
vacation AND sports keywords" ? if so, this is trivial and very fast. just
do a join with the previous result set (saved in a temporary table).
You are right - once you have the keywords it is a simple AND query. The tricky part is once you've selected "vacation", what are the keywords (the subset) that will be displayed to the user? That query is:
- select all photos that have the keyword 'vacation' - return all (distinct) keywords of all those photos
This query is performed often - it is what I'm trying to optimise. It gets slightly more complex as you drill down. For example, say "vacation" and "sports" are selected. The query is then:
- select all photos that have the keywords 'vacation' and 'sports' - return all (distinct) keywords of all those photos
and, are you sure this is a hierarchical data base ? it sounds like the typical many-to-many with split relation, which cannot be represented hirerarchically, since the data is *not* hierarchical in nature.
No, the data is not inherently hierarchical at all. I'm kind of faking it by massaging it into something that looks hierarchical. I've had a very hard time communicating what I'm trying to accomplish, but I do have working code that I'm happy with. I just need to speed up my searches by an order of magnitude or two. I'm trying to do it in memory since I have not yet come up with a clever way to simplify the actual queries.
Thanks again for your help!
Demitri
