Michael, > I think I read that the 'partition_tag' can be anything, char(), int, > anything really, as long as you can define it in the $sql_partition tag > in amavisd.conf.
Yes, as far as amavisd is concerned. > however, on the mysql 5.1 site taking about partitions, it says they > must be integer, or resolve to integer. > > Data type of partitioning key. A partitioning key must be either an > integer column or an expression that resolves to an integer. The column > or expression value may also be NULL. (See Section 18.2.6, "How MySQL > Partitioning Handles NULL".) > > http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html > > so, our idea of using 'char(6)' and something like yyyy-ww (so, this > week would be '2008-47') won't work, because I don't think mysql would > do something intelligent converting that to an integer. So why not use an integer week number, as suggested. Or combine it with a year number into a larger integer. $sql_partition_tag = sub { my($msginfo)[EMAIL PROTECTED]; iso8601_week($msginfo->rx_time) }; or: (if using %P in quarantine name template and a 2-char field width is desired): $sql_partition_tag = sub { my($msginfo)[EMAIL PROTECTED]; sprintf("%02d",iso8601_week($msginfo->rx_time)) }; > also, the create tables don't take into account partition schemes. > like: [...] > and, also the suggestion to delete email by partition didn't include the > drop partition table type queries. Right, I didn't provide any howto-with-mysql. > so, while amavisd-new supports a partition_tag, (which again, deleting > by partition TAG might be faster), in order to support real mysql > partitions (including innodb_file_unique.. or whatever that is called), > you will need to do a lot of work. Someone would need to provide a guide for each SQL server type. > upgrading systems in place might be problematic, unless somehow you > leave 'partition_tag 0' as mysql partition 0, and consider deleting it > (dropping partition) next year. Well, yes, or delete it manually in few weeks. Btw, even without partitioned tables, deleting by partition_tag value is often faster than a traditional approach with joins, e.g.: DELETE FROM msgs WHERE partition_tag >= 30 AND partition_tag <= 46; DELETE FROM msgrcpt WHERE partition_tag >= 30 AND partition_tag <= 46; DELETE FROM quarantine WHERE partition_tag >= 30 AND partition_tag <= 46; DELETE FROM maddr WHERE partition_tag >= 30 AND partition_tag <= 46; Mark ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ AMaViS-user mailing list AMaViS-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/amavis-user AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 AMaViS-HowTos:http://www.amavis.org/howto/