Michael, > Currently using mysql 5.1.x , amavisd 2.5.4, innodb tables. > Having major delays on email with penpals enabled on sites with a large > maddr table. > > SELECT msgs.time_num, msgs.mail_id, subject FROM msgs JOIN msgrcpt ON > msgs.mail_id=msgrcpt.mail_id WHERE sid='16' AND rid='2' AND content!='V' > AND ds='P' ORDER BY msgs.time_num DESC LIMIT 1
> Three currious things make the query faster:. > 1) drop the ORDER BY in 'ORDER BY msgs.time_num DESC LIMIT 1' > this will probally not select the latest sent message, thereby > breaking penpals. Right. Dropping ORDER BY would not be good. > 2) drop the LIMIT 1. I don't know if this will break penpals, Mark > would need to tell us this > (maybe 2.6.2 could eliminate the limit 1? and penpals could just > grab the first response? The LIMIT 1 can be safely dropped if it makes a query run faster. The code only collects the first record. > 3) force an index, as in > [...] > either case, if forcing an index helps, should not this be a good > edition to the default? It probably depends much on a type and version of an SQL server. If dropping the LIMIT achieves the same speedup, I would not venture into forcing index, which might make things worse with some other SQL server. Btw, the content!='V' AND ds='P' is not essential. If it would make a significant speedup, one or the other or both conditions could be dropped (at a small risk of a pen-pals false positive). Bernd Probst writes: > in my maddr table are about 400.000 recordsets. > Im using mysql 5.0.x and amavisd-new 2.6.1. > I received an answer within 0.1 second. With 2 million records in msgs and twice that much in msgrcpt my penpals query typically takes less than 10 ms (5 ms average according to amavisd-agent). Using PostgreSQL 8.2.7. But admittedly, we are a small site (1000 users) by ISP standards. > I realized a different problem: the combination of order by and LIMIT does > not always list the last message. According to > http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html MySQL ends > sorting after - in this case - one recordset. There could exist newer > recordsets in the database. Since the penpal bonus score is exponentially > decreasing in time, it might be too low. Are you sure this is a problem? The text says: MySQL ends the sorting as soon as it has found the first row_count rows OF THE SORTED RESULT. This sounds just how it was supposed to be. There is no point in sorting remaining records, as long as the correct result is returned. 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 [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/
