[Semanticscuttle-devel] SQL optimizations in svn trunk!

[Christian W. <cw...@cw...>]

Hi all,


This week I worked on lowering the number of queries that
SemanticScuttle needs to deliver a bookmark listing page.

Just looking at the front page of a SemanticScuttle instance with 10 or
more bookmarks, not logged in, caused 37 SQL queries to be fired
to the database.
If you are logged in, doing the same action took SQL 54 queries!

After my optimizations, it only takes 8 queries for the front page for
anonymous people and 22 for users that are logged in. The logged-in
count can easily be lowered to 13 by combining the watch queries that
are sent for each single bookmark to be displayed into one.

I also tried running SemanticScuttle with 32000 bookmarks which
actually worked, but I got response times of over 1 second. Problem
here seems to be that SemanticScuttle uses COUNT() and DISTINCT for
some queries. This cannot be easily fixed, only by i.e. putting those
counts as hard-coded values into the bookmark rows and updating them
whenever the database changes, or doing that via a cron job.


So the next feature release of SemanticScuttle (0.97) should feel a bit
snappier, especially with many concurrent users requesting pages on your
server.


-- 
Regards/Mit freundlichen Grüßen
Christian Weiske

-=≡ Geeking around in the name of science since 1982 ≡=-