i think i found one reason: key 'mailbox_status' did not contain 'seen_flag' and 'recent_flag'
ALTER TABLE `dbmail`.`dbmail_messages` DROP INDEX `mailbox_status` , ADD INDEX
`mailbox_status` ( `mailbox_idnr` ,
`status` , `seen_flag` , `recent_flag` );
_________________________________
two lines of explain output now changed from 'Using where' to
'Using where; Using index' which sounds better IMHO
that there is still 'Using where' involved may be stupidity of MySQL
_________________________________
mysql> explain SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=8 AND
(status < 2) UNION SELECT 1,COUNT(*)
FROM dbmail_messages WHERE mailbox_idnr=8 AND (status < 2) AND seen_flag=1
UNION SELECT 2,COUNT(*) FROM
dbmail_messages WHERE mailbox_idnr=8 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 | 138 | Using where; Using index |
| 2 | UNION | dbmail_messages | ref |
mailbox_idnr_index,seen_flag_index,status_index,mailbox_status |
mailbox_status | 8 | const | 138 | Using where |
| 3 | UNION | dbmail_messages | range |
mailbox_idnr_index,status_index,mailbox_status |
mailbox_status | 9 | NULL | 138 | Using where |
| NULL | UNION RESULT | <union1,2,3> | ALL | NULL
|
NULL | NULL | NULL | NULL | |
+----+--------------+-----------------+-------+----------------------------------------------------------------+----------------+---------+-------+------+--------------------------+
4 rows in set (0.00 sec)
mysql> explain SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=8 AND
(status < 2) UNION SELECT 1,COUNT(*)
FROM dbmail_messages WHERE mailbox_idnr=8 AND (status < 2) AND seen_flag=1
UNION SELECT 2,COUNT(*) FROM
dbmail_messages WHERE mailbox_idnr=8 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 | 138 | Using where; Using index |
| 2 | UNION | dbmail_messages | range |
mailbox_idnr_index,seen_flag_index,status_index,mailbox_status |
mailbox_status | 9 | NULL | 138 | Using where; Using index |
| 3 | UNION | dbmail_messages | range |
mailbox_idnr_index,status_index,mailbox_status |
mailbox_status | 9 | NULL | 138 | Using where; Using index |
| NULL | UNION RESULT | <union1,2,3> | ALL | NULL
|
NULL | NULL | NULL | NULL | |
+----+--------------+-----------------+-------+----------------------------------------------------------------+----------------+---------+------+------+--------------------------+
4 rows in set (0.00 sec)
Am 10.07.2012 23:23, schrieb Reindl Harald:
> maybe interesting for performance
>
> the samples below are from my workstation with virtually no load
> so i assume with concurrently users this may be significant
>
> besides performance i would love to get them away because i'd
> like to seek for queries not using keys in my own web-apps :-)
>
> 941 lines in 6 minutes with one user is high.......
> dbmail-3.0.2-1339700881.fc17.20120614.rh.8365ef6790d601223447261e6b8e8acf2472191a.x86_64
> _________________________________________________________________
>
> /etc/my.cnf:
> slow_query_log = 1
> slow_query_log_file = /var/log/mysql_slow_query.log
> log_queries_not_using_indexes = 1
> _________________________________________________________________
>
> loaded (/usr/lib/systemd/system/mysqld.service; enabled)
> Active: active (running) since Tue, 10 Jul 2012 23:15:19 +0200; 6min ago
>
> [root@srv-rhsoft:~]$ cat /var/log/mysql_slow_query.log | wc -l
> 941
>
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000354 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955168;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=2 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=2 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=2 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000180 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955168;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=2;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000828 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 1263
> SET timestamp=1341955168;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=8 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=8 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=8 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000217 Lock_time: 0.000031 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955168;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=8;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000234 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955168;
> SELECT k.message_idnr, keyword FROM dbmail_keywords k LEFT JOIN
> dbmail_messages m ON k.message_idnr=m.message_idnr
> LEFT JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr = 8 AND m.status IN (0,1);
> # Time: 120710 23:19:58
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000365 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955198;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=2 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=2 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=2 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000206 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955198;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=2;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000795 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 1263
> SET timestamp=1341955198;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=8 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=8 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=8 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000231 Lock_time: 0.000025 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955198;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=8;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000227 Lock_time: 0.000017 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955198;
> SELECT k.message_idnr, keyword FROM dbmail_keywords k LEFT JOIN
> dbmail_messages m ON k.message_idnr=m.message_idnr
> LEFT JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr = 8 AND m.status IN (0,1);
> # Time: 120710 23:20:00
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000383 Lock_time: 0.000024 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=2 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=2 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=2 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000200 Lock_time: 0.000036 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955200;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=2;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000360 Lock_time: 0.000027 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=48 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=48 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=48 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000342 Lock_time: 0.000025 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=41 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=41 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=41 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000321 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=2 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=2 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=2 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000208 Lock_time: 0.000026 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955200;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=2;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000331 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=5 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=5 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=5 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000333 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=47 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=47 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=47 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000313 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=6 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=6 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=6 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000395 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 12
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=9 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=9 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=9 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000958 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 1695
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=44 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=44 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=44 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000505 Lock_time: 0.000024 Rows_sent: 0 Rows_examined: 1677
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=45 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=45 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=45 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000807 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 1263
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=8 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=8 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=8 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000191 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955200;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=8;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000194 Lock_time: 0.000018 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955200;
> SELECT k.message_idnr, keyword FROM dbmail_keywords k LEFT JOIN
> dbmail_messages m ON k.message_idnr=m.message_idnr
> LEFT JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr = 8 AND m.status IN (0,1);
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000838 Lock_time: 0.000022 Rows_sent: 0 Rows_examined: 1263
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=8 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=8 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=8 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000500 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 201
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=11 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=11 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=11 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000200 Lock_time: 0.000022 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955200;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=8;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000194 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955200;
> SELECT k.message_idnr, keyword FROM dbmail_keywords k LEFT JOIN
> dbmail_messages m ON k.message_idnr=m.message_idnr
> LEFT JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr = 8 AND m.status IN (0,1);
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000734 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 939
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=43 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=43 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=43 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000319 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=4 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=4 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=4 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000326 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=49 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=49 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=49 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.002946 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 9003
> SET timestamp=1341955200;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=46 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=46 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=46 AND (status < 2) AND recent_flag=1;
> # Time: 120710 23:20:01
> # User@Host: afi[afi] @ localhost []
> # Query_time: 0.170065 Lock_time: 0.026328 Rows_sent: 0 Rows_examined: 5
> use afi;
> SET timestamp=1341955201;
> select doc_id from cms1_docmgmt_entrys where doc_timestamp>doc_indexed and
> doc_file_download_ext='pdf';
> # User@Host: contentlounge[contentlounge] @ localhost []
> # Query_time: 0.001665 Lock_time: 0.000153 Rows_sent: 0 Rows_examined: 2
> use contentlounge;
> SET timestamp=1341955201;
> select doc_id from cms1_docmgmt_entrys where doc_timestamp>doc_indexed and
> doc_file_download_ext='pdf';
> # Time: 120710 23:20:02
> # User@Host: thelounge_www[thelounge_www] @ localhost []
> # Query_time: 0.756043 Lock_time: 0.026452 Rows_sent: 0 Rows_examined: 147
> use thelounge_www;
> SET timestamp=1341955202;
> select doc_id from cms1_docmgmt_entrys where doc_timestamp>doc_indexed and
> doc_file_download_ext='pdf';
> # User@Host: vis[vis] @ localhost []
> # Query_time: 0.244070 Lock_time: 0.033506 Rows_sent: 0 Rows_examined: 24
> use vis;
> SET timestamp=1341955202;
> select doc_id from cms1_docmgmt_entrys where doc_timestamp>doc_indexed and
> doc_file_download_ext='pdf';
> # Time: 120710 23:20:30
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000369 Lock_time: 0.000025 Rows_sent: 0 Rows_examined: 3
> use dbmail;
> SET timestamp=1341955230;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=2 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=2 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=2 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000199 Lock_time: 0.000025 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955230;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=2;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000350 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955230;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=2 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=2 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=2 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000176 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955230;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=2;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000329 Lock_time: 0.000018 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955230;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=2 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=2 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=2 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000191 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955230;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=2;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000870 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 1263
> SET timestamp=1341955230;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=8 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=8 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=8 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000183 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955230;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=8;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000212 Lock_time: 0.000042 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955230;
> SELECT k.message_idnr, keyword FROM dbmail_keywords k LEFT JOIN
> dbmail_messages m ON k.message_idnr=m.message_idnr
> LEFT JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr = 8 AND m.status IN (0,1);
> # Time: 120710 23:21:00
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000363 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955260;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=2 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=2 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=2 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000199 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955260;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=2;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000824 Lock_time: 0.000027 Rows_sent: 0 Rows_examined: 1263
> SET timestamp=1341955260;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=8 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=8 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=8 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000179 Lock_time: 0.000022 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955260;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=8;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000203 Lock_time: 0.000033 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955260;
> SELECT k.message_idnr, keyword FROM dbmail_keywords k LEFT JOIN
> dbmail_messages m ON k.message_idnr=m.message_idnr
> LEFT JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr = 8 AND m.status IN (0,1);
> # Time: 120710 23:21:02
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000401 Lock_time: 0.000026 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=2 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=2 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=2 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000343 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=48 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=48 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=48 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000211 Lock_time: 0.000023 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955262;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=2;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000352 Lock_time: 0.000025 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=41 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=41 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=41 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000319 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=2 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=2 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=2 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000176 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955262;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=2;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000321 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=5 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=5 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=5 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000331 Lock_time: 0.000023 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=47 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=47 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=47 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000322 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=6 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=6 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=6 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000375 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 12
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=9 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=9 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=9 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000943 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 1695
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=44 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=44 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=44 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000943 Lock_time: 0.000018 Rows_sent: 0 Rows_examined: 1677
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=45 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=45 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=45 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000839 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 1263
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=8 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=8 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=8 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000188 Lock_time: 0.000025 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955262;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=8;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000222 Lock_time: 0.000028 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955262;
> SELECT k.message_idnr, keyword FROM dbmail_keywords k LEFT JOIN
> dbmail_messages m ON k.message_idnr=m.message_idnr
> LEFT JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr = 8 AND m.status IN (0,1);
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000837 Lock_time: 0.000023 Rows_sent: 0 Rows_examined: 1263
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=8 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=8 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=8 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000476 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 201
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=11 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=11 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=11 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000190 Lock_time: 0.000028 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955262;
> SELECT DISTINCT(keyword) FROM dbmail_keywords k LEFT JOIN dbmail_messages m
> ON k.message_idnr=m.message_idnr LEFT
> JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr=8;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000195 Lock_time: 0.000026 Rows_sent: 0 Rows_examined: 0
> SET timestamp=1341955262;
> SELECT k.message_idnr, keyword FROM dbmail_keywords k LEFT JOIN
> dbmail_messages m ON k.message_idnr=m.message_idnr
> LEFT JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr WHERE
> b.mailbox_idnr = 8 AND m.status IN (0,1);
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000684 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 939
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=43 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=43 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=43 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000330 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=4 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=4 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=4 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.000350 Lock_time: 0.000029 Rows_sent: 0 Rows_examined: 3
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=49 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=49 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=49 AND (status < 2) AND recent_flag=1;
> # User@Host: dbmail[dbmail] @ localhost []
> # Query_time: 0.001543 Lock_time: 0.000012 Rows_sent: 0 Rows_examined: 9003
> SET timestamp=1341955262;
> SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=46 AND (status < 2)
> UNION SELECT 1,COUNT(*) FROM
> dbmail_messages WHERE mailbox_idnr=46 AND (status < 2) AND seen_flag=1 UNION
> SELECT 2,COUNT(*) FROM dbmail_messages
> WHERE mailbox_idnr=46 AND (status < 2) AND recent_flag=1;
>
>
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
--
Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/
http://www.thelounge.net/signature.asc.what.htm
signature.asc
Description: OpenPGP digital signature
_______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
