[MAILBOX-186] Missing indices on JPA mailstore database results in poor mailbox listing as the number of messages grows

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 0.4
• Component/s: jpa
• Assignee: Unassigned
• Reporter: Craig George
• Created: 21/Nov/12 02:47
• Updated: 29/Mar/18 09:48

Description

We have an application which queries the IMAP store of Apache James 3 in the following manner:

final Message[] messages = this.folder.getMessages();
final FetchProfile fetchProfile = new FetchProfile();
fetchProfile.add(Item.ENVELOPE);
fetchProfile.add(Item.FLAGS);
fetchProfile.add(Item.CONTENT_INFO);
fetchProfile.add(IMAPFolder.FetchProfileItem.HEADERS);
fetchProfile.add(IMAPFolder.FetchProfileItem.SIZE);
fetchProfile.add(UIDFolder.FetchProfileItem.UID);
this.folder.fetch(messages, fetchProfile);

When this query is performed on a folder with a large number of messages the response times degrade very quickly. This is due to the following query being run for each message:

SELECT t0.PROPERTY_ID,
t0.PROPERTY_LINE_NUMBER,
t0.PROPERTY_LOCAL_NAME,
t0.PROPERTY_NAME_SPACE,
t0.PROPERTY_VALUE
FROM MA_DIRECT_APACHE_JAMES.JAMES_MAIL_PROPERTY t0
WHERE t0.MAILBOX_ID = :1
AND t0.MAIL_UID = :2
ORDER BY t0.PROPERTY_LINE_NUMBER ASC

The problem is that in the JAMES_MAIL_PROPERTY table only the PROPERTY_LINE_ID column is indexed, implicity by virtue of the fact that it is the primary key. The workaround is to add the following indexes on the table:

CREATE INDEX I_JMS_MIL_PROPERTY_MSG_ID ON JAMES_MAIL_PROPERTY (MAILBOX_ID, MAIL_UID);
CREATE INDEX I_JMS_MIL_PROPERTY_LINE_NUMBER ON JAMES_MAIL_PROPERTY (PROPERTY_LINE_NUMBER);