Jake Anderson wrote:
Giulio Ferro wrote:
I'm having serious problems in a production environment with
slow queries.
S.o.: freebsd 7 stable amd64. DB: mysql5.1.28 compiled from source
The query that slows the db is:
SELECT messageblk, is_header FROM dbmail_messageblks WHERE
physmessage_id = xxxx ORDER BY messageblk_idnr
I've set up the db as instructed in the dbmail wiki. Everything was
smooth
for several days, than these queries have begun to slow the db server.
I know this probably depends on the db and not on dbmail, but any
suggestion
on how to solve this would be greatly appreciated...
Thanks.
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
try doing an explain select, but really that should be one of the
fastest queries in the thing.
how much ram do you have in the machine and how big is the database?
explain SELECT messageblk, is_header FROM dbmail_messageblks WHERE
physmessage_id = xxxx ORDER BY messageblk_idnr
This is the explain:
+----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
| id | select_type | table | type |
possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | dbmail_messageblks | index |
physmessage_id_index,physmessage_id_is_header_index | PRIMARY | 8
| NULL | 2360882 | Using where |
+----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
1 row in set (0.05 sec)
From my test, the problem with this query is the "order by".
Without that, the query takes relatively show time to complete.
Here is the funny thing:
select messageblk_idnr from dbmail_messageblks where physmessage_id=xxx
order by messageblk_idnr;
==> takes about 0.05 sec
SELECT messageblk, is_header FROM dbmail_messageblks WHERE
physmessage_id = xxx ORDER BY messageblk_idnr;
==> takes about 4 minutes
SELECT messageblk, is_header FROM dbmail_messageblks WHERE
physmessage_id = xxx;
==> takes about 0.10 sec
Any idea?
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
