Hi guys,
I'm running dbmail 2.2.10 on ubuntu (installed from source) with mysql
5.0.38. I have mysql set to log queries without indexes to the slow log.
I'm literally getting bombarded with these:
# Time: 080428 15:22:04
# [EMAIL PROTECTED]: dbmail[dbmail] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 3 Rows_examined: 453
SELECT 'a',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=163 AND
(status < 2) UNION SELECT 'b',COUNT(*) FROM dbmail_messages WHERE
mailbox_idnr=163 AND (status < 2) AND seen_flag=1 UNION SELECT
'c',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=163 AND (status <
2) AND recent_flag=1;
# [EMAIL PROTECTED]: dbmail[dbmail] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 3 Rows_examined: 111
SELECT 'a',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=467 AND
(status < 2) UNION SELECT 'b',COUNT(*) FROM dbmail_messages WHERE
mailbox_idnr=467 AND (status < 2) AND seen_flag=1 UNION SELECT
'c',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=467 AND (status <
2) AND recent_flag=1;
# [EMAIL PROTECTED]: dbmail[dbmail] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 3 Rows_examined: 2937
SELECT 'a',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=145 AND
(status < 2) UNION SELECT 'b',COUNT(*) FROM dbmail_messages WHERE
mailbox_idnr=145 AND (status < 2) AND seen_flag=1 UNION SELECT
'c',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=145 AND (status <
2) AND recent_flag=1;
# [EMAIL PROTECTED]: dbmail[dbmail] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 3 Rows_examined: 21
SELECT 'a',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=246 AND
(status < 2) UNION SELECT 'b',COUNT(*) FROM dbmail_messages WHERE
mailbox_idnr=246 AND (status < 2) AND seen_flag=1 UNION SELECT
'c',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=246 AND (status <
2) AND recent_flag=1;
# [EMAIL PROTECTED]: dbmail[dbmail] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 3 Rows_examined: 147
SELECT 'a',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=144 AND
(status < 2) UNION SELECT 'b',COUNT(*) FROM dbmail_messages WHERE
mailbox_idnr=144 AND (status < 2) AND seen_flag=1 UNION SELECT
'c',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=144 AND (status <
2) AND recent_flag=1;
# [EMAIL PROTECTED]: dbmail[dbmail] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 3 Rows_examined: 3
SELECT 'a',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=66 AND
(status < 2) UNION SELECT 'b',COUNT(*) FROM dbmail_messages WHERE
mailbox_idnr=66 AND (status < 2) AND seen_flag=1 UNION SELECT
'c',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=66 AND (status <
2) AND recent_flag=1;
# [EMAIL PROTECTED]: dbmail[dbmail] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 3 Rows_examined: 3
SELECT 'a',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=64 AND
(status < 2) UNION SELECT 'b',COUNT(*) FROM dbmail_messages WHERE
mailbox_idnr=64 AND (status < 2) AND seen_flag=1 UNION SELECT
'c',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=64 AND (status <
2) AND recent_flag=1;
mysql> explain SELECT 'a',COUNT(*) FROM dbmail_messages WHERE
mailbox_idnr=163 AND (status < 2) UNION SELECT 'b',COUNT(*) FROM
dbmail_messages WHERE mailbox_idnr=163 AND (status < 2) AND seen_flag=1
UNION SELECT 'c',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=163
AND (status < 2) AND recent_flag=1;
+----+--------------+-----------------+-------+----------------------------------------------------------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type |
possible_keys |
key | key_len | ref | rows | Extra |
+----+--------------+-----------------+-------+----------------------------------------------------------------+----------------+---------+------+------+--------------------------+
| 1 | PRIMARY | dbmail_messages | range |
mailbox_idnr_index,status_index,mailbox_status |
mailbox_status | 9 | NULL | 49 | Using where; Using index |
| 2 | UNION | dbmail_messages | range |
mailbox_idnr_index,seen_flag_index,status_index,mailbox_status |
mailbox_status | 9 | NULL | 49 | Using where |
| 3 | UNION | dbmail_messages | range |
mailbox_idnr_index,status_index,mailbox_status |
mailbox_status | 9 | NULL | 49 | Using where |
| NULL | UNION RESULT | <union1,2,3> | ALL |
NULL |
NULL | NULL | NULL | NULL | |
+----+--------------+-----------------+-------+----------------------------------------------------------------+----------------+---------+------+------+--------------------------+
The function which handles this is int db_getmailbox_count() within
db.c, i'm thinking is it worth having 3 separate smaller queries that
will use the indexes properly rather than a single query with unions ?
It'll be a fairly easy change, i'm happy to write a patch?
The code is currently:
/* count messages */
snprintf(query, DEF_QUERYSIZE,
"SELECT 'a',COUNT(*) FROM %smessages WHERE
mailbox_idnr=%llu "
"AND (status < %d) UNION "
"SELECT 'b',COUNT(*) FROM %smessages WHERE
mailbox_idnr=%llu "
"AND (status < %d) AND seen_flag=1 UNION "
"SELECT 'c',COUNT(*) FROM %smessages WHERE
mailbox_idnr=%llu "
"AND (status < %d) AND recent_flag=1",
DBPFX, mb->uid, MESSAGE_STATUS_DELETE, //
MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN,
DBPFX, mb->uid, MESSAGE_STATUS_DELETE, //
MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN,
DBPFX, mb->uid, MESSAGE_STATUS_DELETE); //
MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN);
if (db_query(query) == -1) {
TRACE(TRACE_ERROR, "query error");
return DM_EQUERY;
}
if (db_num_rows()) {
exists = (unsigned)db_get_result_int(0,1);
seen = (unsigned)db_get_result_int(1,1);
recent = (unsigned)db_get_result_int(2,1);
}
mb->exists = exists;
mb->unseen = exists - seen;
mb->recent = recent;
What do you think?
Simon
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
