As any long-time GoldMine user will know, emails in GoldMine should be filed (or fast-filed) rather than left in the inbox. As discussed in a previous post (here), an overflowing inbox can stop new emails appearing; what wasn’t mentioned there is that it can also have a very serious impact on performance.

Here are a some SQL scripts which can be run from within GoldMine to flag up offending users.

No. 1: how many emails are in people’s inboxes, largest number first:


select userid, count(userid) from mailbox where folder='x-gm-inbox' group by userid order by count(userid) desc


No. 2: how many emails, by month received, by user, largest number first:


select cast(datepart(year, maildate) as varchar(4))+'-'+right('00'+cast(datepart(month, maildate) as varchar(2)),2)+' '+userid as usermonth,
count(cast(datepart(year, maildate) as varchar(4))+'-'+right('00'+cast(datepart(month, maildate) as varchar(2)),2)+' '+userid)
from mailbox where folder='x-gm-inbox'
group by cast(datepart(year, maildate) as varchar(4))+'-'+right('00'+cast(datepart(month, maildate) as varchar(2)),2)+' '+userid
order by count(cast(datepart(year, maildate) as varchar(4))+'-'+right('00'+cast(datepart(month, maildate) as varchar(2)),2)+' '+userid) desc


No. 3: similar to 2, but in date order, oldest first:


select cast(datepart(year, maildate) as varchar(4))+'-'+right('00'+cast(datepart(month, maildate) as varchar(2)),2)+' '+userid as usermonth,
count(cast(datepart(year, maildate) as varchar(4))+'-'+right('00'+cast(datepart(month, maildate) as varchar(2)),2)+' '+userid)
from mailbox where folder='x-gm-inbox'
group by cast(datepart(year, maildate) as varchar(4))+'-'+right('00'+cast(datepart(month, maildate) as varchar(2)),2)+' '+userid
order by cast(datepart(year, maildate) as varchar(4))+'-'+right('00'+cast(datepart(month, maildate) as varchar(2)),2)+' '+userid


No. 4: how many emails are in people’s trashes, largest number first:


select userid, count(userid) from mailbox where folder='x-gm-trash' group by userid order by count(userid) desc