With this query you will find all unconnected headervalues:
select * from dbmail_headervalue where id not in (select headervalue_id
from dbmail_header);
In my case there are 245,257 unused values while purging 4000 messages, so
I could do a cronjob with:
delete from dbmail_headervalue where id not in (select headervalue_id
from dbmail_header);
daily or once a week, but this should be done by dbmail-util in my
opinion, in or after db_icheck_physmessages.
Am 02.04.2012, 13:21 Uhr, schrieb Reindl Harald <h.rei...@thelounge.net>:
Am 02.04.2012 12:45, schrieb Paul J Stevens:
On 04/02/2012 12:28 PM, Reindl Harald wrote:
this is not really a problem and is triggered
by the fact the we truncated only the header-tables
while the references table had already data
Correct.
more a problem is that headervalues is growing and
growing over the time and after few months it
uses more index-space as the rest of the database
Prune it!
http://blog.dbmail.eu/archives/12-Pruning-the-dbmail_headervalue-table.html
however - how is this supposed to work?
DELETE FROM dbmail_headername WHERE headername='received' or
headername='recieved' or headername='user-agent' or
headername='Received';
this will remove the 'headername'-records
but not anything in `dbmail_headervalue` which is the table with the
hughe record-count
______________________________
CREATE TABLE `dbmail_header` (
`physmessage_id` bigint(20) unsigned NOT NULL,
`headername_id` bigint(20) unsigned NOT NULL,
`headervalue_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`physmessage_id`,`headername_id`,`headervalue_id`),
KEY `physmessage_id` (`physmessage_id`),
KEY `headername_id` (`headername_id`),
KEY `headervalue_id` (`headervalue_id`),
KEY `physmessage_id_headername_id` (`physmessage_id`,`headername_id`),
KEY `physmessage_id_headervalue_id`
(`physmessage_id`,`headervalue_id`),
KEY `headername_id_headervalue_id` (`headername_id`,`headervalue_id`),
CONSTRAINT `dbmail_header_ibfk_1` FOREIGN KEY (`physmessage_id`)
REFERENCES `dbmail_physmessage` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_header_ibfk_2` FOREIGN KEY (`headername_id`)
REFERENCES `dbmail_headername` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_header_ibfk_3` FOREIGN KEY (`headervalue_id`)
REFERENCES `dbmail_headervalue` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
CREATE TABLE `dbmail_headername` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`headername` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `headername` (`headername`)
) ENGINE=InnoDB AUTO_INCREMENT=1936 DEFAULT CHARSET=utf8
ROW_FORMAT=COMPRESSED;
CREATE TABLE `dbmail_headervalue` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`hash` varchar(255) NOT NULL,
`headervalue` text NOT NULL,
`sortfield` varchar(255) DEFAULT NULL,
`datefield` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `hash` (`hash`),
KEY `headervalue` (`headervalue`(255)),
KEY `sortfield` (`sortfield`),
KEY `datefield` (`datefield`)
) ENGINE=InnoDB AUTO_INCREMENT=568824 DEFAULT CHARSET=utf8
ROW_FORMAT=COMPRESSED;
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
