For an intial test, I used this patch (to 2_2):
diff --git a/db.c b/db.c
index b001adc..7136bee 100644
--- a/db.c
+++ b/db.c
@@ -2743,12 +2743,12 @@ char *db_imap_utf7_like(const char *column,
}
if (has_sensitive_part) {
- g_string_printf(like, "%s %s '%s%s' AND %s %s '%s%s'",
+ g_string_printf(like, "%s %s '%s%s' AND lower(%s) %s
lower('%s%s')",
column, db_get_sql(SQL_SENSITIVE_LIKE), sensitive,
filter,
- column, db_get_sql(SQL_INSENSITIVE_LIKE), insensitive,
filter);
+ column, db_get_sql(SQL_SENSITIVE_LIKE), insensitive,
filter);
} else {
- g_string_printf(like, "%s %s '%s%s'",
- column, db_get_sql(SQL_INSENSITIVE_LIKE), insensitive,
filter);
+ g_string_printf(like, "lower(%s) %s lower('%s%s')",
+ column, db_get_sql(SQL_SENSITIVE_LIKE), insensitive,
filter);
}
tmplike = like->str;
That improved my gnus startup time (noting that the query in question
gets called once for each group during gnus' startup due to imap EXAMINE
calls for each group) from OTOO 26 minutes to OTOO 1 minute.
The combination of my fast list patch (which used a with clause to
enable dbail-imapd to use a single sql query to get all of the info it
needs to service an imap LIST command) and the above WIP patch reduce
my 40k group startup time from nearly an hour to a couple of minutes
(gnus needs a minute or so to parse all of the IMAP data).
Obviously most IMAP users do not split incoming mail as finely as I do,
but a server with hundreds or thousands of users also should benefit
from eliminating ILIKE in favour of LOWER() and LIKE. DBmail already
added in the index needed to make this change useful, presumably
thinking that ILIKE would use it. But ILIKE does not.
-JimC
--
James Cloos <[email protected]> OpenPGP: 1024D/ED7DAEA6
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
