shlomif has asked for the wisdom of the Perl Monks concerning the following question:
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.