> > - set the pragma "temp_store" to MEMORY > - CREATE TEMPORARY VIEW temp_table AS (<select whole table>) > > I am assuming that temp_table is completely in memory, and any queries > against it will not go back to the disk. Since the table itself is > small, I am hoping that the overhead of reading the entire table into > RAM will be shadowed by the speed improvement of the largish number of > queries that don't have to go to the disk. Does this sound reasonable? > > -- I suspect not. creating a view does not actually generate any data it is only when you SELECT from that view that any data access happens. so your scheme is not caching into RAM at all.
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. 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). 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. it may well be that you have a pseudo-problem, created by a GUI that does not match the nature of the data.
