A NOTE has been added to this issue.
======================================================================
http://dbmail.org/mantis/view.php?id=709
======================================================================
Reported By: yavo
Assigned To:
======================================================================
Project: DBMail
Issue ID: 709
Category: Database layer
Reproducibility: always
Severity: tweak
Priority: normal
Status: new
target:
======================================================================
Date Submitted: 14-Jun-08 09:45 CEST
Last Modified: 14-Jun-08 11:18 CEST
======================================================================
Summary: Some sql optimizations
Description:
The following queries can be rewritten to optimize them
db.c line 3009
snprintf(query, DEF_QUERYSIZE, "SELECT message_idnr+1 FROM %smessages "
"WHERE mailbox_idnr=%llu "
"ORDER BY message_idnr DESC LIMIT 1",DBPFX, mb->uid);
to
snprintf(query, DEF_QUERYSIZE, "SELECT MAX(message_idnr)+1 FROM
%smessages "
"WHERE mailbox_idnr=%llu ", DBPFX, mb->uid);
db.c line 4021
snprintf(query, DEF_QUERYSIZE,
"SELECT message_idnr FROM %smessages "
"WHERE mailbox_idnr = %llu "
"AND status < %d AND seen_flag = 0 "
"ORDER BY message_idnr LIMIT 1",DBPFX,
mailbox_idnr, MESSAGE_STATUS_DELETE);
to
snprintf(query, DEF_QUERYSIZE,
"SELECT MIN(message_idnr) FROM %smessages "
"WHERE mailbox_idnr = %llu "
"AND status < %d AND seen_flag = 0 ",DBPFX,
mailbox_idnr, MESSAGE_STATUS_DELETE);
======================================================================
----------------------------------------------------------------------
paul - 14-Jun-08 11:18
----------------------------------------------------------------------
The reason for *not* using MAX or MIN is the poor performance of those on
postgresql. Has this changed?
Proposed performance tweaks without documentation and/or benchmarks (or at
the very least EXPLAIN analysis) are a bit suspect imo.
Feel free to discuss this on the list please.
Issue History
Date Modified Username Field Change
======================================================================
14-Jun-08 09:45 yavo New Issue
14-Jun-08 11:18 paul Note Added: 0002568
======================================================================
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
