dbmail-dev at tech
Apr 28, 2008, 7:30 AM
Post #1 of 6
(306 views)
Permalink
|
|
db.c/db_getmailbox_count - unions not using indexes correctly
|
|
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
> # User[at]Host: 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;
> # User[at]Host: 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;
> # User[at]Host: 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;
> # User[at]Host: 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;
> # User[at]Host: 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;
> # User[at]Host: 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;
> # User[at]Host: 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;
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
Dbmail-dev[at]dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
|
