Re: [Dbmail-dev] STORE is SLOOOOOW on folder with many, many messages

[Kevin Brown]

I wrote:
> I'll create a patch using it instead of what I proposed earlier.

And it's attached.  Dirt simple, really.

Note that the patch is relative to the mailfilters patch that Paul
supplies in his 2.0.7+20050927-1 version of the DBMail Debian package.
But if I'm not mistaken, that should just change the line numbers
involved.



-- 
Kevin Brown                                           [EMAIL PROTECTED]

#! /bin/sh /usr/share/dpatch/dpatch-run
## 05_mailboxspeed.dpatch by  <[EMAIL PROTECTED]>
##
## All lines beginning with `## DP:' are a description of the patch.
## DP: Patch to speed up mailbox access

@DPATCH@

--- dbmail-2.0/db.c.orig        2005-12-11 09:55:57.078036000 -0800
+++ dbmail-2.0/db.c     2005-12-15 13:20:09.388681507 -0800
@@ -2896,6 +2896,16 @@
 
        /* count messages */
        snprintf(query, DEF_QUERYSIZE,
+                "SELECT count(*), "
+                "count(CASE WHEN seen_flag <> 0 THEN 1 ELSE NULL END), "
+                "count(CASE WHEN recent_flag <> 0 THEN 1 ELSE NULL END) "
+                "FROM dbmail_messages "
+                "WHERE mailbox_idnr = '%d' AND "
+                "status IN ('%d', '%d')",
+                mb->uid, MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN);
+
+       /*
+       snprintf(query, DEF_QUERYSIZE,
                         "SELECT 'a',COUNT(*) FROM dbmail_messages WHERE 
mailbox_idnr='%llu' "
                         "AND (status='%d' OR status='%d') UNION "
                         "SELECT 'b',COUNT(*) FROM dbmail_messages WHERE 
mailbox_idnr='%llu' "
@@ -2905,15 +2915,16 @@
                         mb->uid, MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN,
                         mb->uid, MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN,
                         mb->uid, MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN);
-       
+       */
+
         if (db_query(query) == -1) {
               trace(TRACE_ERROR, "%s,%s: query error", __FILE__, __func__);
               return -1;
         }
        
-       exists = (unsigned)db_get_result_int(0,1);
-       seen   = (unsigned)db_get_result_int(1,1);
-       recent = (unsigned)db_get_result_int(2,1);
+       exists = (unsigned)db_get_result_int(0,0);
+       seen   = (unsigned)db_get_result_int(0,1);
+       recent = (unsigned)db_get_result_int(0,2);
 
        mb->exists = exists;
        mb->unseen = exists - seen;