Composite indexes

Hi,

I read your manual about defining Indexes for JPA Entities (http://www.objectdb.com/java/jpa/entity/index), but I still have some problems understanding how and when should I use them.

Let's start with some example then. Assume we have an entity that represents an interior design image, called Inspiration :

public class Inspiration {

@Id
Long id;

String room; // type of the room (kitchen, bedroom etc.)
Date lastModificationDate; // date of last modification
Float rating; //users rating in scale from 1.0 to 10.0


@ElementCollection(fetch = FetchType.EAGER)
List<Integer> filterDescription; // some integers describing photo characteristic

Boolean isPublished; // either true or false

(...)
}

Here are my questions regarding composite indexes :

#1. First of all - the first member in composite index should be a field that is most commonly used in WHERE clauses or maybe the filed that is used in ORDER clause for query that returns the biggest result set? Or a field that returns the smallest subset of unique records (like for example "isPublished" which is either true or false, then "room" which has 7-8 unique values etc...)

#2. Can I use the same field as secondary member in many @Indexes? Something like that :

@Index(members = { "lastModificationDate", "isPublished" })

@Index(members = { "author", "isPublished" })

I use isPublished field in almost every query, and the fileds "lastModificationDate" and "author" are used in ORDER clause in most of my queries.

#3. As you can see, there is also a field named "filterDescription" - that is a list of Integers. When I search for a picture that fulfils some filter criteria, I use queries similar to this :

SELECT i FROM Inspiration i
WHERE i.room = "bedroom" AND
      4 MEMBER OF i.filterDescription AND
      3 MEMBER OF i.filterDescription AND
      i.isPublished = true
ORDER BY i.lastModificationDate

Basing on this example, could you suggest how to make most efficient indexes ? Maybe I should redesign my entity so it want be using a List<Integer>?

#4. Are @Index field used also on relations? @OneToOne, @ManyToOne? I don't want to start with complex example, so maybe I'll try to explain my doubts regarding more complex queries later.

1. Field Order in a Composite Index

The usability of a composite index depends on the order of the indexed fields as explained in the Indexes in Queries section of the manual. In a query that searches for exact match of several fields, any order would be fine. If you also need to get ordered results, then make sure that the requested order matches the index order. If you want to use the index also for queries that use part of the fields - the fields in the queries should come first in the index.

2. Same Field in Several Indexes

It is supported. ObjectDB will try to select the most suitable index for every query.

3. Indexing List<Integer>

You can index that field. It should accelerate queries on that field only. For more complicated queries you will have to check performance with and without such an index.

4. Are @Index field used also on relations? @OneToOne, @ManyToOne?

Yes. They are useful when the query includes navigation between entities (directly or using JOIN).