Manitou-Mail Blog

Recently I wanted to reduce the size of my main manitou-mail database,and thus I’ve decided to delete all the messages I’ve received from some mailing-lists. I know these messages are archived elsewhere,so that I could re-import them if needed anyway.
But I didn’t like the idea of deleting also the messages that I’ve sent to these mailing-lists,because it would have broken the rule I’ve adopted of keeping all sent messages. Also on second thought,I thought it would be best to include also the whole threads in which I’ve participated,so that the context of the messages would still be available (BTW,the entire thread to which a message belongs can be recalled in the user interface by the contextual menu command:“Show thread on new page”).

So the question was,how to select all the messages tagged with certain tags,but excluding all threads for which at least one message has the Sent status? As usual,the database and the SQL come to help. First I looked up the tag_id’s of the tags corresponding to the mailing-lists,let’s say they were 3,6 and 10. And then I just expressed in SQL the sentence above. The result is:
SELECT mt.mail_id FROM mail_tags mt JOIN mail m1 USING (mail_id) WHERE tag IN (3,6,10) AND NOT EXISTS (SELECT 1 FROM mail m2 WHERE m2.thread_id=m1.thread_id AND m2.status&128!=0)

After issuing this query with the selection dialog,with the Limit To field empty to ensure that all messages are retrieved,all that was needed to accomplish the task was to select all the messages in the resulting list (Ctrl-A) and hit the Del key.

For manitou-mail installations that use a lot of filters,it may be a good idea to check from time to time which ones are still useful and which ones are unused.
Since all the filters are evaluated for each incoming message (except if a stop action is encountered),keeping around a large number of obsolete filters may have an adverse impact on CPU usage.
Fortunately,manitou-mdx gathers statistics on filter hits,so it’s easy to find out which filters no longer generate any hit,with the help of some SQL.
Let’s start with a query that retrieve filters that never had any hit:

SELECT expr_id,name FROM filter_expr LEFT JOIN filter_log USING (expr_id) WHERE filter_log.expr_id is null;

Now it may be that some of the filters returned by this query are new so that no hit on them occurred yet. We need to filter out these by adding a condition on the last_update field,requesting that the filter hasn’t been modified or created as new since at least 3 months.
Also,we only want entries from filter_expr that have actions tied on them,because filters without actions can be used as sub-expressions (that’s advanced filter usage) and don’t generate any hit.
With these additional conditions,the query becomes:

SELECT DISTINCT expr_id,name from filter_action JOIN filter_expr USING (expr_id) LEFT JOIN filter_log using (expr_id) WHERE filter_log.expr_id is null AND filter_expr.last_update<now()-'3 months'::interval;


With the query above we can check in advance what we’re about to delete.

Now,the deletion itself needs two steps,one for the filter_action table and another for filter_expr. Since both tables are joined in the query,we need a preliminary step to save the expr_id to delete into a temporay table. The SQL sequence including the transaction is:

BEGIN;

CREATE TEMPORARY TABLE del_expr AS
SELECT DISTINCT expr_id from filter_action JOIN filter_expr USING (expr_id)
LEFT JOIN filter_log using (expr_id)
WHERE filter_log.expr_id is null
AND filter_expr.last_update<now()-'3 months'::interval;

DELETE FROM filter_action WHERE expr_id IN (SELECT expr_id from del_expr);

DELETE FROM filter_expr WHERE expr_id IN (SELECT expr_id from del_expr);

COMMIT;

To additionally delete the filters that haven’t been used for a significant period of time (for example one year),they could be added to our temporary table before the above deletion:

INSERT INTO del_expr SELECT expr_id
FROM filter_log GROUP BY expr_id
HAVING max(hit_date)<now()-'1 year'::interval)

Happy filters cleaning!