evoteam team mailing list archive

[Daniel Hahler <launchpad@xxxxxxxxxx>]

Public bug reported:

The following query result should get cached, unless tag associations
get changed.

For example by having a cache key on the blog (which is NULL in this
case), status and anything else relevant.

When anything part of this key gets changed, the cached result should
get invalidated and fetched/calculated anew on the next access.

AFAIK this is partly implemented, but probably not for this query (and
others which could benefit from this).

Caching widget output (it's the tag widget in this case) is a good way to "work around" this, but like page level caching it's the granularity that may get missed.
I agree however that when the tag widget would cache it's output based on the same keys this query would use, this would be OK with this particular case.

Query #100: Get tags
SELECT LOWER(tag_name) AS tag_name, post_datestart, COUNT(DISTINCT itag_itm_ID) AS tag_count
FROM evo_items__tag INNER JOIN evo_items__itemtag ON itag_tag_ID = tag_ID INNER JOIN evo_items__item ON itag_itm_ID = post_ID
WHERE 1
  AND post_status = 'published'
  AND post_datestart < '2009-12-19 00:13'
GROUP BY tag_name
ORDER BY tag_count DESC
LIMIT 50

** Affects: b2evolution
     Importance: Wishlist
         Status: Triaged


** Tags: cache performance

** Changed in: b2evolution
       Status: New => Triaged

** Changed in: b2evolution
   Importance: Undecided => Wishlist

** Tags added: cache performance

-- 
SQL queries should use cache key which gets invalidated when something relevant changes
https://bugs.launchpad.net/bugs/498387
You received this bug notification because you are a member of Evoteam,
which is subscribed to b2evolution.

Status in b2evolution: Triaged

Bug description:
The following query result should get cached, unless tag associations get changed.

For example by having a cache key on the blog (which is NULL in this case), status and anything else relevant.

When anything part of this key gets changed, the cached result should get invalidated and fetched/calculated anew on the next access.

AFAIK this is partly implemented, but probably not for this query (and others which could benefit from this).

Caching widget output (it's the tag widget in this case) is a good way to "work around" this, but like page level caching it's the granularity that may get missed.
I agree however that when the tag widget would cache it's output based on the same keys this query would use, this would be OK with this particular case.

Query #100: Get tags
SELECT LOWER(tag_name) AS tag_name, post_datestart, COUNT(DISTINCT itag_itm_ID) AS tag_count
FROM evo_items__tag INNER JOIN evo_items__itemtag ON itag_tag_ID = tag_ID INNER JOIN evo_items__item ON itag_itm_ID = post_ID
WHERE 1
  AND post_status = 'published'
  AND post_datestart < '2009-12-19 00:13'
GROUP BY tag_name
ORDER BY tag_count DESC
LIMIT 50