-- COMMENT: Drop unnecessary indexes, add new ones for dbmail queries

ALTER TABLE dbmail_acl DROP INDEX user_id_index;

ALTER TABLE dbmail_header
    DROP INDEX physmessage_id,
    DROP INDEX physmessage_id_headername_id,
    DROP INDEX physmessage_id_headervalue_id,
    DROP INDEX headername_id_headervalue_id;

ALTER TABLE dbmail_headervalue
    DROP INDEX hash,
    DROP INDEX headervalue,
    DROP INDEX sortfield,
    DROP INDEX datefield,
    ADD INDEX hash_headervalue (hash, headervalue(255));

ALTER TABLE dbmail_mailboxes
    DROP INDEX name_index,
    DROP INDEX owner_idnr_index;

ALTER TABLE dbmail_messages
    DROP INDEX physmessage_id_index,
    DROP INDEX seen_flag_index,
    DROP INDEX unique_id_index,
    DROP INDEX status_index,
    DROP INDEX mailbox_idnr_index,
    ADD INDEX unique_seen_index (physmessage_id, seen_flag, unique_id),
    ADD INDEX status_mailbox_index (physmessage_id, mailbox_idnr, status);

ALTER TABLE dbmail_partlists DROP INDEX physmessage_id;

ALTER TABLE dbmail_sievescripts
    DROP INDEX owner_idnr_2,
    DROP INDEX name,
    DROP INDEX owner_idnr,
    ADD UNIQUE INDEX owner_idnr (owner_idnr, name);

ALTER TABLE dbmail_subscription DROP INDEX user_id_index;

ALTER TABLE dbmail_envelope DROP INDEX physmessage_id_2;

