Without seeing your queries, I'm not sure what slows you down. Bear in mind that SQLite will use at most one index for any given query - so you should look at the generated query plan for your queries to determine if the indices you gave are actually used.

From how I interpret the documentation, SQLite will also benefit from an early reduction of the JOIN size, that is, you need to order the JOINs of tables yourself so that the initial resultset is as small as possible.

Of course, you can likely pre-cache the browsing hierarchy and optimize it for fast reads (and slow writes) by employing some triggers on the main table and the normalized browsing relationship table, and create the flattened tree with the triggers. SQLite supports triggers, but MySQL does not, so if you're planning on porting this to other systems, beware.

What problems did you find with SQLite3 that SQLite2 doesn't have ?

In reply to Re: Optimizing Tree Hierarchies with DBD::SQLite2 by Corion
in thread Optimizing Tree Hierarchies with DBD::SQLite2 by shlomif

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.