A NOTE has been added to this issue.
======================================================================
http://dbmail.org/mantis/view.php?id=823
======================================================================
Reported By: waza123
Assigned To:
======================================================================
Project: DBMail
Issue ID: 823
Category: IMAP daemon
Reproducibility: always
Severity: minor
Priority: normal
Status: new
target:
======================================================================
Date Submitted: 22-Dec-09 00:21 CET
Last Modified: 22-Dec-09 12:37 CET
======================================================================
Summary: Slow query
Description:
Why imap_headerinfo() php function is so slow ?
MySQL slow query logs:
# Time: 091222 1:18:10
# u...@host: dbmail[dbmail] @ localhost []
# Query_time: 28 Lock_time: 0 Rows_sent: 0 Rows_examined: 5581190
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id JOIN
dbmail_headername n ON h.headername_id=n.id JOIN dbmail_headervalue v ON
h.headervalue_id=v.id WHERE m.mailbox_idnr = 21 AND m.message_idnr BETWEEN
755013 AND 779875 AND lower(n.headername) IN
('date','to','cc','from','subject','x-priority','importance','priority','content-type');
See, query time: 28 seconds.. wtf ? thanks.
======================================================================
----------------------------------------------------------------------
(0002954) waza123 (reporter) - 22-Dec-09 00:23
http://dbmail.org/mantis/view.php?id=823#c2954
----------------------------------------------------------------------
More info:
dbmail_header table row count: 6,966,169
size in mb: 643.9 MiB
----------------------------------------------------------------------
(0002955) waza123 (reporter) - 22-Dec-09 02:01
http://dbmail.org/mantis/view.php?id=823#c2955
----------------------------------------------------------------------
php 5.2.8
----------------------------------------------------------------------
(0002956) waza123 (reporter) - 22-Dec-09 02:01
http://dbmail.org/mantis/view.php?id=823#c2956
----------------------------------------------------------------------
dbmail latest git
----------------------------------------------------------------------
(0002957) waza123 (reporter) - 22-Dec-09 02:42
http://dbmail.org/mantis/view.php?id=823#c2957
----------------------------------------------------------------------
This query also slow:
SELECT message_idnr,envelope FROM dbmail_envelope e JOIN dbmail_messages m
ON m.physmessage_id=e.physmessage_id WHERE m.mailbox_idnr = 21 AND
message_idnr BETWEEN 707649 AND 723732;
Query took 2.9010 sec
Server load: 0-5 % cpu
----------------------------------------------------------------------
(0002958) waza123 (reporter) - 22-Dec-09 02:44
http://dbmail.org/mantis/view.php?id=823#c2958
----------------------------------------------------------------------
I added INDEX for message_idnr table: dbmail_envelope , now query very fast
%)
----------------------------------------------------------------------
(0002959) paul (administrator) - 22-Dec-09 11:12
http://dbmail.org/mantis/view.php?id=823#c2959
----------------------------------------------------------------------
Please explain your last statement, because there is no message_idnr field
on the dbmail_envelope table.
----------------------------------------------------------------------
(0002960) waza123 (reporter) - 22-Dec-09 12:37
http://dbmail.org/mantis/view.php?id=823#c2960
----------------------------------------------------------------------
i mean: physmessage_id
Issue History
Date Modified Username Field Change
======================================================================
22-Dec-09 00:21 waza123 New Issue
22-Dec-09 00:23 waza123 Note Added: 0002954
22-Dec-09 02:01 waza123 Note Added: 0002955
22-Dec-09 02:01 waza123 Note Added: 0002956
22-Dec-09 02:42 waza123 Note Added: 0002957
22-Dec-09 02:44 waza123 Note Added: 0002958
22-Dec-09 11:12 paul Note Added: 0002959
22-Dec-09 12:37 waza123 Note Added: 0002960
======================================================================
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
