Pruning the dbmail_headervalue table

In DBMail 2.2 the dbmail_headervalue table will contain all header-values for all messages in store. Even for medium-sized installations this can easily result in a very large table.

However, since that table is just about only used for IMAP search you might consider dropping a couple of headernames from the cache.

For example:

DELETE FROM dbmail_headername WHERE headername = 'received';
will delete from dbmail_headervalue all entries regarding the Received header.

To determine which headernames should be dropped you could use a view like this:

CREATE VIEW header_count AS 
    SELECT count(1) AS count, n.id, n.headername 
    FROM dbmail_headervalue v 
    LEFT JOIN dbmail_headername n ON v.headername_id=n.id 
    GROUP BY n.id;

After that you can do:

SELECT * FROM header_count ORDER BY count; 
and delete all headernames from dbmail_headername for those headers you deem unlikely to ever be used in IMAP search.
DELETE FROM dbmail_headername WHERE headername = 'Received';

Doing this for a couple of the most prolific - but un-used headers - will drastically reduce the size of the dbmail_headervalue tables. But remember you will have to keep an eye on the header_count, and re-issue the delete queries on a regular basis.

Trackbacks

    No Trackbacks

Comments

Display comments as (Linear | Threaded)

    No comments


Add Comment


Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA