Active Objects Composite Indexes and Foreign Keys

1

Hello,
I am going to use composite indexes for Active Objects, I’m refactoring existing code that only has indexes on single fields at this time. [AO 1.2.0 upgrade guide](http://AO 1.2.0 upgrade guide)

Javadoc:
https://javadoc.io/static/net.java.dev.activeobjects/activeobjects/3.1.7/net/java/ao/schema/Indexed.html
https://javadoc.io/static/net.java.dev.activeobjects/activeobjects/3.1.7/net/java/ao/schema/Index.html

I have some queries that join a foreign key field (the ID of a foreign key AO) and some other local field. Is it possible (or necessary) to create a composite index for such a query?

For example:

public interface Parent extends Entity {
  
  @OneToMany(reverse = "getParent")
  getChilds()
...

@Indexes({
        // TODO: HOW?  IS THIS RIGHT?  OR NECESSARY?
        @Index(name="parent_something", methodNames = {"getParent", "getSomething"})
})
public interface Child extends Entity {
  getParent()
  setParent()

  @Indexed   // for queries on only this field
  getSomething()
  setSomething()

 doSomeQuery() {
    ... "PARENT_ID = ? AND SOMETHING = ?" ...  // composite index required?
  }
...
3

As it turns out, it just works (FK composite indexes). Great job, whoever implemented the AO indexing!

We create a composite index for 2 AO fields, one of which is a foreign key to another AO (List) and the other which is a property/field of our AO (archived), like this:

@Indexes({
        @Index(name = "list_archived", methodNames = {"getList", "isArchived"})
})

where List is a FK field for the List AO. The “archived” field is in this AO.

results in this in the indexes for the your AO table in the DB:

INDEX_NAME                                           COLUMN_NAME
-------------                                            -----------
INDEX_AO_9355AF_CAR201396793     LIST_ID
INDEX_AO_9355AF_CAR201396793     ARCHIVED

Ture Hoefner

2 Likes