As part of my day job, I'm working on an application. In the application there are items, which have a canonical hierarchy, and a browsing hierarcy. I'm using DBD::SQLite2 with Class::DBI.

I'm using the DBIx::Tree::NestedSet module for the canonical hierarcy, specified in the items. What happens is that every item only has one parent. This uses the nested set tree mechanism (references for what it does are in the docs for it.

I'm using a different table (items_browse) for the browsing "hieararchy". This is a simple parent_id many-to-many child_id relationship. The browsing hierarchy may also contain circles.

I'm using the following indexes:
CREATE INDEX index_items_lft ON items ( lft ),
CREATE INDEX index_items_rgt ON items ( rgt ),
CREATE INDEX index_item_browse_parent_id ON item_browse ( parent_id , child_id ),
CREATE INDEX index_item_browse_child_id ON item_browse ( child_id ),

Now, I populated the items from a flat file, into both the canonical and browsing hieararchies. It has about 10,000 records. However, then the web-interface I built for it is incredibly slow. (100 queries take 42 seconds). What indexes do I need to use to improve its performance?

I'm not using DBD::SQLite (with SQLite 3) because its DBD driver has some bugs that I found and was unable to fix. MySQL seems to have some limitations on the query as well, and I'm leaning towards converting everything to PostgreSQL, but did not do it yet, and didn't get the approval of my boss.

In reply to 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.