Add time-based cutoffs for public tag cloud, favorited lists to speed up those queries.

Defaulting to only looking at last 90 days of activity, can be adjusted up or down.

$config['tag']['cutoff'] = 86400 * 90;
$config['popular']['cutoff'] = 86400 * 90;

Per-user and per-group tag clouds do not use the cutoff (and it doesn't help with indexing on them).

1 files changed, 3 insertions, 0 deletions

diff --git a/actions/publictagcloud.php b/actions/publictagcloud.php
index 9e4478dbb..9993b2d3f 100644
--- a/actions/publictagcloud.php
+++ b/actions/publictagcloud.php
@@ -106,7 +106,10 @@ class PublictagcloudAction extends Action
         #Add the aggregated columns...
         $tags->selectAdd('max(notice_id) as last_notice_id');
         $calc = common_sql_weight('created', common_config('tag', 'dropoff'));
+        $cutoff = sprintf("notice_tag.created > '%s'",
+                          common_sql_date(time() - common_config('tag', 'cutoff')));
         $tags->selectAdd($calc . ' as weight');
+        $tags->addWhere($cutoff);
         $tags->groupBy('tag');
         $tags->orderBy('weight DESC');