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)
This is good, it says type=index, which means it will use the index to
speed up the query.
So the database scheme for that query seems to be good.
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
Well that is certainly odd, that the ordering adds that much time.
Anyhow, I ran the
querries on my 10GB database of dbmail and it does take less then 0,05
seconds.
I think you should try to increase your innodb_buffer_pool within your
mysql config.
Any idea?
You can also check mysql.com for "database tuning", they have a few
guides and some
nice web cast on that topic, including stuff from Jay Pipes, just
Google the name, you will find
it for sure. Also mysql performance blog is a good point to start, and
focus on the innodb
related parameters.
Also if you post your hardware configuration and the my.cnf here
somebody might have a
hint for you.
greetings,
Daniel
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
