summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorBrion Vibber <brion@pobox.com>2010-02-04 11:06:01 -0800
committerBrion Vibber <brion@pobox.com>2010-02-04 11:06:01 -0800
commit5a1cbdc6f1e32be7b8430924a1125422d8457584 (patch)
tree28147de9aef4dd9fcec909e2ca0a13cc7d204cff
parent7a7e2162dd7eed59e60d9360d8692abc111d940c (diff)
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).
-rw-r--r--actions/favorited.php3
-rw-r--r--actions/publictagcloud.php3
-rw-r--r--lib/default.php6
-rw-r--r--lib/grouptagcloudsection.php1
-rw-r--r--lib/personaltagcloudsection.php1
-rw-r--r--lib/popularnoticesection.php7
6 files changed, 17 insertions, 4 deletions
diff --git a/actions/favorited.php b/actions/favorited.php
index 9ffa5b844..d8980440d 100644
--- a/actions/favorited.php
+++ b/actions/favorited.php
@@ -186,10 +186,13 @@ class FavoritedAction extends Action
function showContent()
{
$weightexpr = common_sql_weight('fave.modified', common_config('popular', 'dropoff'));
+ $cutoff = sprintf("fave.modified > '%s'",
+ common_sql_date(time() - common_config('popular', 'cutoff')));
$qry = 'SELECT notice.*, '.
$weightexpr . ' as weight ' .
'FROM notice JOIN fave ON notice.id = fave.notice_id ' .
+ "WHERE $cutoff " .
'GROUP BY id,profile_id,uri,content,rendered,url,created,notice.modified,reply_to,is_local,source,notice.conversation ' .
'ORDER BY weight DESC';
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');
diff --git a/lib/default.php b/lib/default.php
index 2bedc4bf0..485a08ba4 100644
--- a/lib/default.php
+++ b/lib/default.php
@@ -144,9 +144,11 @@ $default =
'invite' =>
array('enabled' => true),
'tag' =>
- array('dropoff' => 864000.0),
+ array('dropoff' => 864000.0, # controls weighting based on age
+ 'cutoff' => 86400 * 90), # only look at notices posted in last 90 days
'popular' =>
- array('dropoff' => 864000.0),
+ array('dropoff' => 864000.0, # controls weighting based on age
+ 'cutoff' => 86400 * 90), # only look at notices favorited in last 90 days
'daemon' =>
array('piddir' => '/var/run',
'user' => false,
diff --git a/lib/grouptagcloudsection.php b/lib/grouptagcloudsection.php
index 14ceda085..f1106cc7b 100644
--- a/lib/grouptagcloudsection.php
+++ b/lib/grouptagcloudsection.php
@@ -59,6 +59,7 @@ class GroupTagCloudSection extends TagCloudSection
function getTags()
{
$weightexpr = common_sql_weight('notice_tag.created', common_config('tag', 'dropoff'));
+ // @fixme should we use the cutoff too? Doesn't help with indexing per-group.
$names = $this->group->getAliases();
diff --git a/lib/personaltagcloudsection.php b/lib/personaltagcloudsection.php
index 091425f92..5ea3f188d 100644
--- a/lib/personaltagcloudsection.php
+++ b/lib/personaltagcloudsection.php
@@ -59,6 +59,7 @@ class PersonalTagCloudSection extends TagCloudSection
function getTags()
{
$weightexpr = common_sql_weight('notice_tag.created', common_config('tag', 'dropoff'));
+ // @fixme should we use the cutoff too? Doesn't help with indexing per-user.
$qry = 'SELECT notice_tag.tag, '.
$weightexpr . ' as weight ' .
diff --git a/lib/popularnoticesection.php b/lib/popularnoticesection.php
index fbf9a60ab..296ddbbb5 100644
--- a/lib/popularnoticesection.php
+++ b/lib/popularnoticesection.php
@@ -59,12 +59,15 @@ class PopularNoticeSection extends NoticeSection
}
}
$weightexpr = common_sql_weight('fave.modified', common_config('popular', 'dropoff'));
+ $cutoff = sprintf("fave.modified > '%s'",
+ common_sql_date(time() - common_config('popular', 'cutoff')));
$qry = "SELECT notice.*, $weightexpr as weight ";
if(isset($tag)) {
$qry .= 'FROM notice_tag, notice JOIN fave ON notice.id = fave.notice_id ' .
- "WHERE notice.id = notice_tag.notice_id and '$tag' = notice_tag.tag";
+ "WHERE $cutoff and notice.id = notice_tag.notice_id and '$tag' = notice_tag.tag";
} else {
- $qry .= 'FROM notice JOIN fave ON notice.id = fave.notice_id';
+ $qry .= 'FROM notice JOIN fave ON notice.id = fave.notice_id ' .
+ "WHERE $cutoff";
}
$qry .= ' GROUP BY notice.id,notice.profile_id,notice.content,notice.uri,' .
'notice.rendered,notice.url,notice.created,notice.modified,' .