On Mon, 25 Mar 2013 17:41:42 +0200 Pavlo Lavrenenko <sa...@portaone.com> wrote:
> Hi. Here are changes for mysql and oracle schemas we currently use > (attached). > > The idea is basically the same as Harald pointed out: remove indexes > that are not in use, add a couple of new, required by dbmail db queries: > > ALTER TABLE dbmail_headervalue > ADD INDEX hash_headervalue (hash, headervalue(255)); > > ALTER TABLE dbmail_messages > ADD INDEX unique_seen_index (physmessage_id, seen_flag, unique_id), > ADD INDEX status_mailbox_index (physmessage_id, mailbox_idnr, > status); > > ALTER TABLE dbmail_sievescripts > ADD UNIQUE INDEX owner_idnr (owner_idnr, name); I applied these to our production mysql (and dropped those you recomended in the attached files) and immediately noticed about 10x drop in Innodb row operations. However looking at slow query log I still see some queries pop up often. This one is the most common: SELECT message_idnr FROM dbmail_messages m LEFT JOIN dbmail_physmessage p ON m.physmessage_id=p.id LEFT JOIN dbmail_datefield ON m.physmessage_id=dbmail_datefield.physmessage_id WHERE m.mailbox_idnr = 3733 AND m.status IN (0,1) ORDER BY sortfield,message_idnr; These take anywhere from 2 to 15 seconds to complete. Can you recommend additional indexing statements to speedup these? -- Jure Pečar http://jure.pecar.org _______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
