Hello,
Thanks for the fast reply.
When I try the patch, this is the result :
[root@server iRedAdmin-Pro-MySQL-1.3.0]# patch -p4 --dry-run < /root/delete_amavisd_db.patch
patching file libs/amavisd/log.py
Hunk #1 FAILED at 38.
1 out of 1 hunk FAILED -- saving rejects to file libs/amavisd/log.py.rej
I read the patch and I think this is for LDAP because I don't find the same function used :
patch : self.db.query
in libs/amavisd/log.py : I found self.db.delete
---
EDIT :
I created a new index :
mysql> ALTER TABLE msgs ADD INDEX msgs_idx_time_content (`time_num`,`content`);
And now :
mysql> EXPLAIN SELECT msgrcpt.* FROM msgrcpt INNER JOIN msgs ON msgrcpt.mail_id=msgs.mail_id WHERE msgs.content IN ('U', 'M', 'H', 'O', 'C') AND msgs.time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY));
+----+-------------+---------+-------+----------------------------------------------------------+-----------------------+---------+----------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+----------------------------------------------------------+-----------------------+---------+----------------------+-------+--------------------------+
| 1 | SIMPLE | msgs | range | msgs_idx_time_num,msgs_idx_content,msgs_idx_time_content | msgs_idx_time_content | 4 | NULL | 35856 | Using where; Using index |
| 1 | SIMPLE | msgrcpt | ref | mail_id | mail_id | 18 | amavisd.msgs.mail_id | 1 | |
+----+-------------+---------+-------+----------------------------------------------------------+-----------------------+---------+----------------------+-------+--------------------------+
2 rows in set (0.00 sec)
So, I edited libs/amavisd/log.py
#self.db.delete(
# 'msgrcpt',
# where='''mail_id IN (SELECT mail_id FROM msgs WHERE %s)''' % sql,
#)
self.db.query('''DELETE msgrcpt.* FROM msgrcpt INNER JOIN msgs ON msgrcpt.mail_id=msgs.mail_id WHERE msgs.content IN ('U', 'M', 'H', 'O', 'C') AND msgs.time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %d DAY))''' % settings.AMAVISD_REMOVE_MAILLOG_IN_DAYS,)
#self.db.delete(
# 'msgs',
# where='%s' % sql,
#)
self.db.delete(
'msgs',
where='''content IN ('U', 'M', 'H', 'O', 'C') \
AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %d DAY))''' % settings.AMAVISD_REMOVE_MAILLOG_IN_DAYS,
)
Now, it works but the query :
DELETE FROM msgs WHERE content IN ('U', 'M', 'H', 'O', 'C') AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY));
has no index :
mysql> EXPLAIN SELECT * FROM msgs WHERE content IN ('U', 'M', 'H', 'O', 'C') AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY));
+----+-------------+-------+------+----------------------------------------------------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------------------------------------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | msgs | ALL | msgs_idx_time_num,msgs_idx_content,msgs_idx_time_content | NULL | NULL | NULL | 88658 | Using where |
+----+-------------+-------+------+----------------------------------------------------------+------+---------+------+-------+-------------+
1 row in set (0.12 sec)
It tooks me many second to log me in iRedAdmin.
