cool - this seems to work perfectly shouldn't it be a part of dbmail-util? Am 02.04.2012 14:03, schrieb ITronic Harald Leithner: > > 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
-- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm
signature.asc
Description: OpenPGP digital signature
_______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
