Efficient query on EmbeddedId

Hi!

I have an entity class with an @EmbeddedId (field1 int and field2 int). Based on the manual, I understand that making a query just on entity.id.field1 is efficient. Of course, making a query containing the whole EmbeddedId is the ideal case, and I use it, but I need more use cases.

My question is: what about making a query just on entity.id.field2? I suppose it will not be an efficient query, because all database entries have to be checked. My assumption is based on the fact that entity.id.field1 and entity.id.field2 form a composite index together, in the order in which they were declared.

In this case, can I just declare an additional index on entitiy.id.field2 in the entity class as @Indices({@Index(members = {"id.field2"})}) or do I have to duplicate entity.id.field2 into entity.field2 and then set an index on it?

I am really interested in this question. Thank you in advance.

Because field1 and field2 form an embedded ID, i.e. serve as the primary key:

1. All the objects of this entity class are stored in the database ordered by field1, field2, which has a similar effect of defining a composite index on ordinary non ID fields (although not exactly the same).
2. You can not use these fields in additional indexes that you define. Only non ID fields can be part of user defined indexed.

Accordingly queries on field1 (and on both fields) will be fast, and queries on field2 may be slow, since as you wrote an entire database scan is required. Notice, that a query on field2 may be slower than a query on the second field (non ID) in an ordinary composite index, since a full scan of the data is required rather than just the index, which is smaller.

In that case, the solution that you suggested of duplicating field2, although doesn't look good, is the practical workaround.

Thank you. I understand.

Another question about indexes: is there any insert/query performance difference between defining an index on a primitive field or on an @Entity field?

Example: int carId or @ManyToOne Car car (which has an int id) (I know the annotation is not necessary in ODB, but it's just as example)

There is a small difference. An index on an int value is a bit more efficient.

This is because a reference type is larger than an int value in ObjectDB internal data representation, so an index on a relationship requires more space (i.e. more database pages), which means more work to manage it, and possibly harder to cache everything.

However, the real difference in a real application could be negligible.

Great! Thank you for clarifying.