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.

This entry was posted by Paul J Stevens on Saturday, February 13. 2010 at 11:09. and is filed under database. You can leave a response, or trackback from your own blog.
Defined tags for this entry:
Related entries by tags:
A RESTful interface is coming
DBMail 2.3.6 released
Interfacing with the DBMail database

Trackbacks

Trackback specific URI for this entry

    No Trackbacks

Comments

Display comments as (Linear | Threaded)

  1. Anonymous says:
    #1 2010-02-18 04:01 (Reply)

    Very helpful blog :-) After deleting the records from dbmail_headername don't forget to run dbmail-util -by -vv And always a good idea to stop all dbmail-lmtpd/impad/pop3d daemons before doing so.

  2. Jonathan Feally says:
    #2 2010-03-09 08:24 (Reply)

    This is the correct syntax to create the view, CREATE VIEW header_count AS SELECT count(1) AS count, n.id, n.headername FROM dbmail_header h LEFT JOIN dbmail_headername n ON h.headername_id=n.id GROUP BY n.id; And running of dbmail-util -by should not be necessary after deleting some cached headers.

  3. Robert C. Sheets says:
    #3 2010-03-10 08:04 (Reply)

    You say that the dbmail_headervalue table is "just about only used for IMAP search" ... what else is it used for? Is it possible that this other use might be important someday? Deleting data that will never be used is very tempting, but I'd like to know what else the data is used for before deleting it. Thanks :-)

  4. Ann says:
    #4 2010-07-15 09:15 (Reply)

    Thanks for information!

  5. Anonymous says:
    #5 2011-03-09 09:20 (Reply)

    I guess DELETE FROM dbmail_headername deletes from the dbmail_headername table, right? I would rather do DELETE FROM dbmail_headervalue.

  6. does nono work says:
    #6 2011-11-28 15:33 (Reply)

    dbmail is a very good software


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