While seraching for the reason of the load on my server checked the used
indices and the unused on.
The query already discussed in the other thread does not use a good index,
it uses mailbox_idnr_index.
SELECT seen_flag, answered_flag, deleted_flag, flagged_flag, draft_flag,
recent_flag, DATE_FORMAT(internal_date, GET_FORMAT(DATETIME,'ISO')),
rfcsize, message_idnr FROM dbmail_messages m LEFT JOIN dbmail_physmessage
p ON p.id = m.physmessage_id WHERE m.mailbox_idnr = 1465 AND m.status IN
(0,1) ORDER BY message_idnr ASC;
I created a new index: mailbox_idnr_status_index (mailbox_idnr, status)
and the load time for the same query drop from 1,7 seconds to 0,06 seconds.
as side effect the query:
SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=718 AND (status
< 2) UNION SELECT 1,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=718
AND (status < 2) AND seen_flag=1 UNION SELECT 2,COUNT(*) FROM
dbmail_messages WHERE mailbox_idnr=718 AND (status < 2) AND recent_flag=1;
uses the same index for the query before the first union.
so I created two more indices:
CREATE INDEX mailbox_idnr_status_seen_index ON dbmail_messages
(mailbox_idnr, status, seen_flag) USING BTREE;
CREATE INDEX mailbox_idnr_status_recent_index ON dbmail_messages
(mailbox_idnr, status, recent_flag) USING BTREE;
and dropped the mailbox_idnr_status_index index.
The load on my server drop to "zero"
More investigion is needed but the messages table has many indices that
all must be maintained so I tried to find indices that are not used by
dbmail. For this I use pt-index-usage on my slow log for 0.3 queries so
this is not really representative:
ALTER TABLE `dbmail`.`dbmail_acl` DROP KEY `user_id_index`; --
type:non-unique
ALTER TABLE `dbmail`.`dbmail_aliases` DROP KEY `alias_index`, DROP KEY
`client_idnr_index`; -- type:non-unique
ALTER TABLE `dbmail`.`dbmail_filters` DROP KEY `dbmail_filters_ibfk_1`; --
type:non-unique
ALTER TABLE `dbmail`.`dbmail_header` DROP KEY `headername_id`, DROP KEY
`headername_id_headervalue_id`, DROP KEY `headervalue_id`, DROP KEY
`physmessage_id_headername_id`, DROP KEY `physmessage_id_headervalue_id`;
-- type:non-unique
ALTER TABLE `dbmail`.`dbmail_headervalue` DROP KEY `datefield`, DROP KEY
`headervalue`, DROP KEY `sortfield`; -- type:non-unique
ALTER TABLE `dbmail`.`dbmail_mailboxes` DROP KEY `name_index`, DROP KEY
`seq`; -- type:non-unique
ALTER TABLE `dbmail`.`dbmail_messages` DROP KEY `status_index`; --
type:non-unique
ALTER TABLE `dbmail`.`dbmail_partlists` DROP KEY `part_id`, DROP KEY
`physmessage_id`; -- type:non-unique
Paul do you have any comment on them, maybe a list of the indices and why
exists and are used?
--
Harald Leithner
ITronic
Vogelweidplatz 12, 1150 Wien, Austria
Tel: +43-1-786 23 88
Fax: +43-1-98 52 077
Mobil: +43-699-123 78 4 78
Mail: [email protected] | itronic.at
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
