On 4/26/06, Cami <[EMAIL PROTECTED]> wrote: > > The time_num on tables quarantine and msgrcpt was used in > > some on the pre-releases of 2.4.0, but I decided not to include > > it in the final 2.4.0 in favour of DELETE CASCADE, as on > > my setup (still at MySQL then) there was no noticeable > > advantage of deleting based on time_num (both were slow, > > taking ten(s) of minutes to delete 100.000 records, even if > > having time_num indexed). I tried with indexes kept during > > deletions, as well as by removing unneeded indexes before > > the purge and re-creating them after. I don't know why Cami > > sees deletes in msgrcpt based on time_num to be significantly faster. > >> I'm assuming that Mark has benchmarked the difference in > >> speed between the time_num index approach vs the DELETE > >> CASCADE approach and found it to be quicker. > >> Mark, can you confirm what your findings were? > > > > > > I didn't find it any slower, so I went for a cleaner approach. > > > > More feedback/experience from heavy SQL users is most welcome. > > Without indexes it was taking roughly 2->5 hours to do a purge. > With indexes it was taking 10->15 minutes at most. (20gig of > quarantined data at the time). > > Perhaps someone else can do some testing and report their > findings. > > Cami > >
Not exactly sure what you are trying to say here. I thought the discussion was about REFERENCEs vs using 'time_num' on the 'quarantine' and 'msgrcpt' tables. I think references is in almost every way advantageous. First couple of things that come to mind is: 1. Duplicating data (time_num) in a database over several tables is never good design when the data can be acquired in a JOIN. Although minimal, it is unnecessary. 2) More time_num columns means more indices, which means more index overhead and space required to hold the indices 3) References have integrity, there can no longer be orphaned records which would be meaningless and third party applications can not cause inconsistencies as well. 4) Simplifies cleanup by cutting down to 2 queries, one DELETE on the 'msgs' table and one on the 'maddr' table. I am not qualified to say how much internal optimization of cascading deletes plays a part when doing these cleanups, but i dont expect it to be slower than it already is. ------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid0709&bid&3057&dat1642 _______________________________________________ AMaViS-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/amavis-user AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 AMaViS-HowTos:http://www.amavis.org/howto/
