CODEWITHSUNDEEP
androidsqliteindexingandroid-room
Sdghasemi
Sdghasemi

Reputation: 5598

How to create partial index in Android Room

I'd like to create a partial index on my table named Messages on State and Id columns. Normally this can be done by adding a WHERE clause to create index statement as described in the official docs. The proper SQLite statement would be:

CREATE INDEX `index_Messages_State_Id` ON `Messages` (`State`, `Id`) WHERE State = -2

Since I'm currently using Room as my ORM, I don't know how to declare a partial index using Room syntax.

Here is a brief of my Messages table:

@Entity(tableName = "Messages")
public class Message implements Parcelable {
    @PrimaryKey
    @SerializedName("i")
    @ColumnInfo(name = "Id")
    private long mId;

    @SerializedName("t")
    @ColumnInfo(name = "Type")
    private byte mType;

    @SerializedName("s")
    @ColumnInfo(name = "State")
    private byte mState;

    ....
}

Thanks in advance.

Upvotes: 3

Views: 412

Answers (1)

Eston Karumbi
Eston Karumbi

Reputation: 66

I don't think there's a defined way of doing this in Room (as at July 22nd, 2019).

On my team, we got around this blocker by defining the partial index inside a migration.

database.execSQL("CREATE INDEX `index_Messages_State_Id` ON `Messages` (`State`, `Id`) WHERE State = -2")

NB: This might not work if your Android version is less than 21. Partial index only work on SQLite 3.8.0 and above, which is supported in API >= 21

Upvotes: 5

Related Questions