From 61251790bd1da10eebee3c56a70a9711e7680ce8 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Mon, 12 Oct 2015 20:58:02 +0200 Subject: Notifications: Allow max 500 notifies per user + add SQL index on uid Turns out that fetching whether or not you have unread notifications (done on every pageview if you're logged in) was pretty slow. The index speeds up both that query and the "my notifications" view. The extra purge for old notifications for users with more than 500 notifications ensures that the index stays effective for the unread notifications count. Otherwise it'll have to read half of the notifications table anyway to check the 'unread' filter. --- lib/Multi/Maintenance.pm | 2 ++ 1 file changed, 2 insertions(+) (limited to 'lib/Multi') diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 23fb6db5..23e17fff 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -93,6 +93,8 @@ my %dailies = ( cleansessions => q|DELETE FROM sessions WHERE lastused < NOW()-'1 month'::interval|, cleannotifications => q|DELETE FROM notifications WHERE read < NOW()-'1 month'::interval|, + cleannotifications2=> q|DELETE FROM notifications WHERE id IN ( + SELECT id FROM (SELECT id, row_number() OVER (PARTITION BY uid ORDER BY id DESC) > 500 from notifications) AS x(id,del) WHERE x.del)|, rmunconfirmusers => q|DELETE FROM users WHERE registered < NOW()-'1 week'::interval AND NOT email_confirmed|, cleanthrottle => q|DELETE FROM login_throttle WHERE timeout < NOW()|, ); -- cgit v1.2.3