Hi,
I have the following entity class:
@Entity @Table @Indices({ @Index(members={"relationshipType", "party"}), @Index(members={"relationshipType", "otherParty"}), @Index(members={"relationshipType"}) }) @XmlRootElement public class Relationship implements Comparable<Relationship> { public static enum RelationshipType { PARENT, OWNER, ADMIN, CONTAINS, MEMBER, EMPLOYEE, MANAGER } private BaseObject party; private BaseObject otherParty; @Enumerated(EnumType.STRING) private RelationshipType relationshipType; }
With the following query:
TypedQuery<BaseObject> query = em().createQuery( "SELECT r.party FROM Relationship r WHERE r.otherParty = :otherParty_id AND r.party.subclassName = :pclass AND r.relationshipType=RelationshipType."+type.toString(), BaseObject.class); query.setParameter("otherParty_id", otherParty); query.setParameter("pclass", partyClass);
We have noticed that this query slows down in a linear way as the number of relationships and BaseObjects grow.
The query is using two indexed fields (otherParty and relationshipType) but subclassName is not indexed, would that be the cause for the slow query?
Is there any other index that should be added to make this query faster?
Thanks in advance,
Eitan