Android Room @Relation and Indexes

Question

I have this class in my Room setup, which combines data from my states table with the corresponding data from my countries table:

public class StateWithCountryData {

    @Embedded
    public State state;

    @Relation(parentColumn = "country_id", entityColumn = "_id", entity = Country.class)
    public Country country;
}

As you can see in my @Relation code, my states.country_id column is joined to my countries._id column.

What I would like to know is whether the @Relation code will create an automatic index on states.country_id or do I need to create the index myself in my State entity class?

If the latter, how do I add the required index to my State entity class? Here it is:

@Entity(tableName = "states")
public class State {

    @PrimaryKey
    @ColumnInfo(name = "_id")
    private long stateId;

    @NonNull
    @ColumnInfo(name = "state_name")
    private String stateName;

    @ColumnInfo(name = "country_id")
    private long countryId;

    @ColumnInfo(name = "last_modified")
    private Date lastModified;

    public State(long stateId, @NonNull String stateName, long countryId, Date lastModified) {
        this.stateId = stateId;
        this.stateName = stateName;
        this.countryId = countryId;
        this.lastModified = lastModified;
    }

    public long getStateId() {
        return stateId;
    }

    public void setStateId(long stateId) {
        this.stateId = stateId;
    }

    @NonNull
    public String getStateName() {
        return stateName;
    }

    public void setStateName(@NonNull String stateName) {
        this.stateName = stateName;
    }

    public long getCountryId() {
        return countryId;
    }

    public void setCountryId(long countryId) {
        this.countryId = countryId;
    }

    public Date getLastModified() {
        return lastModified;
    }

    public void setLastModified(Date lastModified) {
        this.lastModified = lastModified;
    }
}

Answer 1

What I would like to know is whether the @Relation code will create an automatic index on states.country_id or do I need to create the index myself in my State entity class?

The way to check is to look at the generated code for the @database e.g. you would get something like :-

final SupportSQLiteOpenHelper.Callback _openCallback = new RoomOpenHelper(configuration, new RoomOpenHelper.Delegate(1) {
  @Override
  public void createAllTables(SupportSQLiteDatabase _db) {
    _db.execSQL("CREATE TABLE IF NOT EXISTS `Country` (`id` INTEGER NOT NULL, `name` TEXT, PRIMARY KEY(`id`))");
    _db.execSQL("CREATE TABLE IF NOT EXISTS `states` (`_id` INTEGER NOT NULL, `state_name` TEXT NOT NULL, `country_id` INTEGER NOT NULL, `last_modified` TEXT, PRIMARY KEY(`_id`))");
    _db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
    _db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '008d8fa1f719c970d7a1182e9e43f80b')");
  }
.....

As you can see the answer is no.

If the latter, how do I add the required index to my State entity class?

You can use :-

@Entity(tableName = "states"
    , indices = {@Index(name = "ixCountryId", value = "country_id")} //<<<<<<<<<< ADDED
    )

And now the generated code is :-

@Override
  protected SupportSQLiteOpenHelper createOpenHelper(DatabaseConfiguration configuration) {
    final SupportSQLiteOpenHelper.Callback _openCallback = new RoomOpenHelper(configuration, new RoomOpenHelper.Delegate(1) {
      @Override
      public void createAllTables(SupportSQLiteDatabase _db) {
        _db.execSQL("CREATE TABLE IF NOT EXISTS `Country` (`id` INTEGER NOT NULL, `name` TEXT, PRIMARY KEY(`id`))");
        _db.execSQL("CREATE TABLE IF NOT EXISTS `states` (`_id` INTEGER NOT NULL, `state_name` TEXT NOT NULL, `country_id` INTEGER NOT NULL, `last_modified` TEXT, PRIMARY KEY(`_id`))");
        _db.execSQL("CREATE INDEX IF NOT EXISTS `ixCountryId` ON `states` (`country_id`)");
        _db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
        _db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '74ee12325d1720c24abff0e5ce479d81')");

i.e. the line

 _db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS `ixCountryId` ON `states` (`country_id`)");

has been added.

A simpler option is to code at the @ColumnInfo level instead of coding indicies at the @Entity level e.g.

@ColumnInfo(name = "country_id", index = true)
private long countryId;

This will auto-generate the index name and result, in the above case, in the following being included in the generated code (instead of the previously shown line):-

_db.execSQL("CREATE INDEX IF NOT EXISTS `index_states_country_id` ON `states` (`country_id`)");

You could also use both (not something that you would do as it's a waste/inefficient) which would result in :-

    _db.execSQL("CREATE INDEX IF NOT EXISTS `ix01` ON `states` (`country_id`)");
    _db.execSQL("CREATE INDEX IF NOT EXISTS `index_states_country_id` ON `states` (`country_id`)");

• coding at the @Entity level offers greater options such as complex (multi-column) or unique indexes.

or do I need to create the index myself in my State entity class?

Not necessarily and perhaps probably not as the index appears to have a negative impact, although depending upon the number of countries and states, if quite low, then perhaps a negligible impact.

Consider the following test code which creates 10000 countries and 500000 states randomly allocated to countries (approx 50 states per country).

Using two core queries :-

SELECT * FROM states WHERE country_id > 500;
SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500;

before and after the creation of the index the timings prior to indexing are

SELECT * FROM states WHERE country_id > 500
> OK
> Time: 0.563s


SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500
> OK
> Time: 0.074s

and after the creation of the index are :-

SELECT * FROM states WHERE country_id > 500
> OK
> Time: 2.764s


SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500
> OK
> Time: 0.158s

So slower with the index (about 5 times slower for the first query and 9.5 slower for the second query).

---

The code also does an EXPLAIN QUERY PLAN before the queries are run.

Before Index creation :-

Query 1 :-

Query 2 :-

After Index creation

Query 1 :-

Query 2 :-

The Code used (using Navicat) :-

DROP TABLE IF EXISTS Country;
DROP TABLE IF EXISTS states;
CREATE TABLE IF NOT EXISTS `Country` (`id` INTEGER NOT NULL, `name` TEXT, PRIMARY KEY(`id`));
CREATE TABLE IF NOT EXISTS `states` (`_id` INTEGER NOT NULL, `state_name` TEXT NOT NULL, `country_id` INTEGER NOT NULL, `last_modified` TEXT, PRIMARY KEY(`_id`));

/* Load data into the Country table */
WITH RECURSIVE cte1(counter,base) AS (
    SELECT 1, 'COUNTRY_A'
    UNION ALL SELECT counter+1,'COUNTRY_A' FROM cte1 LIMIT 10000
)
INSERT INTO Country (name) SELECT base||counter FROM cte1; 

/* Load fata into the states table */
WITH RECURSIVE cte1(counter,base,cid) AS (
  SELECT 1,'STATE_S', (abs(random()) % (SELECT count() FROM Country)) + 1
    UNION ALL SELECT 
        counter+1,
        'STATE_S',
      (abs(random()) % (SELECT count() FROM Country)) + 1   
    FROM cte1 LIMIT 500000
)
INSERT INTO states (state_name, country_id) SELECT base||counter, cid FROM cte1; 

EXPLAIN QUERY PLAN
SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500;
EXPLAIN QUERY PLAN
SELECT * FROM states WHERE country_id > 500;

SELECT * FROM states WHERE country_id > 500;
SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500;

/* Create the Index */
CREATE INDEX IF NOT EXISTS `ix01` ON `states` (`country_id`);

EXPLAIN QUERY PLAN
SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500;
EXPLAIN QUERY PLAN
SELECT * FROM states WHERE country_id > 500;

SELECT * FROM states WHERE country_id > 500;
SELECT * FROM states JOIN Country ON Country.id = states.country_id WHERE country_id < 500;

/* Show states per country */
SELECT Country.name,count() AS states_inCountry FROM States JOIN Country ON country_id = Country.id GROUP BY country_id;

/* Clean up */
DROP INDEX IF EXISTS ix01;
DROP TABLE IF EXISTS states;
DROP TABLE If EXISTS Country;

• Note this may be a little bit of an unfair comparison/test as the index creation may have an impact upon the subsequent run conditions. Ideally the queries should be run separately from each other and separate from the index creation.