Hi Ilja,
The wiki looks pretty much empty so I just started playing around.
I've been testing with a messageblks.is_header field so db_fetch_headers
will not retrieve the whole message before parsing.
Looks like we may get something like a factor 2 improvement in searching
headers.
I still have to figure out how to modify message insertion to use such a
field. Perhaps you or Aaron have some bright ideas.
For now I've modified the database by hand using group by queries to
locate messageblk_idnr for the header block and a shell-script to
generate update queries.
queries used:
alter table messageblks add is_header int(1) not null default 0;
update the messageblk table:
#> mysql --skip-column-names -B -e "select messageblk_idnr from
messageblks group by physmessage_id" dbmail |awk 'BEGIN {
printf("\nupdate messageblks set is_header=1 where messageblk_idnr in
("); } { if(NR % 200 == 0) { printf("\nupdate messageblks set
is_header=1 where messageblk_idnr in ("); i=0; } else {
printf("%s,",$1); }}'|sed 's/,$/);/' | mysql dbmail
#> mysql -B -e "select messageblk_idnr from messageblks group by
physmessage_id" dbmail |awk '{ if(NR % 50 == 0) { printf("\nupdate
messageblks set is_header=1 where messageblk_idnr in ("); i=0; } else {
printf("%s,",$1); }}'|sed 's/,$/);/'
Ilja Booij wrote:
Paul J Stevens wrote:
Hi all,
I'm looking to optimize _ic_search without resorting to drastics.
I noticed dbmsgbuf.c retrieves the full message from the database in
two functions. In both cases first the physmessage_id is queried using
the msgid, followed by a query on the messageblks table using the
physmessage_id. Two queries where one suffices. A simple but effective
improvement I think. Query syntax (left join ... using ...) checked on
mysql/postgres (dunno about oracle or ansi-sql).
I also fixed some of the FIXME's ...
Ilja?
Looks good. I'v committed the patch.
PS. Of course searching still sucks major :-( until we get some real
header caching...
true..
Ilja
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
--
________________________________________________________________
Paul Stevens mailto:[EMAIL PROTECTED]
NET FACILITIES GROUP PGP: finger [EMAIL PROTECTED]
The Netherlands________________________________http://www.nfg.nl
Index: rfcmsg.c
===================================================================
RCS file: /cvsroot-dbmail/dbmail/rfcmsg.c,v
retrieving revision 1.10
diff -u -a -d -r1.10 rfcmsg.c
--- rfcmsg.c 2004/03/19 16:27:38 1.10
+++ rfcmsg.c 2004/06/09 22:02:12
@@ -117,7 +117,7 @@
{
int result, level = 0, maxlevel = -1;
- if (db_init_msgfetch(msguid) != 1) {
+ if (db_init_fetch_headers(msguid) != 1) {
trace(TRACE_ERROR,
"db_fetch_headers(): could not init msgfetch\n");
return -2;
@@ -148,7 +148,7 @@
"db_fetch_headers(): trying to fetch at maxlevel
%d...\n",
maxlevel);
- if (db_init_msgfetch(msguid) != 1) {
+ if (db_init_fetch_message(msguid) != 1) {
trace(TRACE_ERROR,
"db_fetch_headers(): could not init
msgfetch\n");
return -2;
@@ -180,7 +180,7 @@
/* ok still problems... try to make a message */
- if (db_init_msgfetch(msguid) != 1) {
+ if (db_init_fetch_message(msguid) != 1) {
trace(TRACE_ERROR,
"db_fetch_headers(): could not init msgfetch\n");
return -2;
Index: dbmsgbuf.c
===================================================================
RCS file: /cvsroot-dbmail/dbmail/dbmsgbuf.c,v
retrieving revision 1.12
diff -u -a -d -r1.12 dbmsgbuf.c
--- dbmsgbuf.c 2004/06/09 11:52:01 1.12
+++ dbmsgbuf.c 2004/06/09 22:02:12
@@ -53,7 +53,8 @@
static unsigned nblocks = 0; /**< number of block */
static const char * tmprow; /**< temporary row number */
-int db_init_msgfetch(u64_t msg_idnr)
+int db_init_fetch_messageblks(u64_t msg_idnr, char *query_template);
+int db_init_fetch_messageblks(u64_t msg_idnr, char *query_template)
{
msgbuf_buf = (char *) my_malloc(sizeof(char) * (size_t)
MSGBUF_WINDOWSIZE);
if (!msgbuf_buf) {
@@ -64,12 +65,7 @@
return 0;
memset(msgbuf_buf, '\0', (size_t) MSGBUF_WINDOWSIZE);
- snprintf(query, DEF_QUERYSIZE,
- "SELECT messageblk FROM messageblks LEFT JOIN messages "
- "USING (physmessage_id) "
- "WHERE messages.message_idnr = '%llu' "
- "ORDER BY messageblk_idnr",
- msg_idnr);
+ snprintf(query, DEF_QUERYSIZE, query_template, msg_idnr);
if (db_query(query) == -1) {
trace(TRACE_ERROR, "%s,%s: could not get message",
@@ -143,6 +139,23 @@
* later use */
db_store_msgbuf_result();
return 1;
+
+}
+
+int db_init_fetch_headers(u64_t msg_idnr)
+{
+ char *query_template = "SELECT messageblk FROM messageblks LEFT JOIN
messages "
+ "USING (physmessage_id) WHERE messages.message_idnr = '%llu' "
+ "AND messageblks.is_header = 1";
+ return db_init_fetch_messageblks(msg_idnr, query_template);
+
+}
+int db_init_fetch_message(u64_t msg_idnr)
+{
+ char *query_template = "SELECT messageblk FROM messageblks LEFT JOIN
messages "
+ "USING (physmessage_id) WHERE messages.message_idnr = '%llu' "
+ "ORDER BY messageblk_idnr";
+ return db_init_fetch_messageblks(msg_idnr, query_template);
}
