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;
signature.asc
Description: OpenPGP digital signature
_______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
