Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Re^2: Optimizing Tree Hierarchies with DBD::SQLite2

by shlomif (Beadle)
on Oct 21, 2005 at 11:19 UTC ( #501959=note: print w/replies, xml ) Need Help??

in reply to Re: Optimizing Tree Hierarchies with DBD::SQLite2
in thread Optimizing Tree Hierarchies with DBD::SQLite2

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.

Hmmm... that could be a big problem.

My queries are the following: from the ID fetch the children in the canonical categorization and the browsing categorization. I'm doing it using the primitives given by DBD::SQLite2 and Class::DBI. From the IDs of the different categories I also refer to their names.


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

Check this bug report.

  • Comment on Re^2: Optimizing Tree Hierarchies with DBD::SQLite2

Replies are listed 'Best First'.
Re^3: Optimizing Tree Hierarchies with DBD::SQLite2
by Corion (Pope) on Oct 21, 2005 at 11:35 UTC

    I once wrote my on categorization thing in a normalized fashion and I found that once I moved queries away from Class::DBI and into SQL JOINs, the results became much faster. This of course meant adding code like the following to the classes:

    __PACKAGE__->set_sql(related_items => <<SQL) SELECT __PRIMARY__ from __TABLE__ INNER JOIN relations ON child = __TABLE__.__PRIMARY__ WHERE relations.parent = ? SQL sub related_items { my ($self) = @_; return $self->search_related_items($self->id); };

    and much uglier JOINs if you are constructing nested sets or junctive queries. I have, for the time being, moved to a denormalized database and found writing the queries much nicer. Performance is acceptable, that is, interactive, for the single user of the system (me).

    If you'll be going the route of JOINing the relation tables, consider writing an extension to SQL::Abstract - I wrote me an (yet unreleased) extension to handle GROUP BY and HAVING clauses, and it worked well for me.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://501959]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2018-05-21 23:08 GMT
Find Nodes?
    Voting Booth?