Room database: What is Index specific columns (indices and @Index) and how to use it?

Question

I was referring Index specific columns of Room Database.

Below is some example code is written here https://developer.android.com/training/data-storage/room/defining-data#column-indexing

Example Code-1:

    @Entity(indices = {@Index("name"),
        @Index(value = {"last_name", "address"})})
public class User {
    @PrimaryKey
    public int id;

    public String firstName;
    public String address;

    @ColumnInfo(name = "last_name")
    public String lastName;

    @Ignore
    Bitmap picture;
}

Example Code-2:

@Entity(indices = {@Index(value = {"first_name", "last_name"},
        unique = true)})
public class User {
    @PrimaryKey
    public int id;

    @ColumnInfo(name = "first_name")
    public String firstName;

    @ColumnInfo(name = "last_name")
    public String lastName;

    @Ignore
    Bitmap picture;
}

This is described in room documentation by android, I have used indices for the uniqueness of column but what does above code means can anyone explain it?

Q1: What is the use of indices and @Index?
Q2: What is the difference between @Index("name") and @Index(value = {"last_name", "address"})?

Index is not for uniqueness. It is for faster searching on a given column. How to generate unique values can be found here: https://stackoverflow.com/a/44109830/1860868 — Derek K, Feb 26 '19 at 16:14
index over `name` is for one column, index over `{"first_name", "last_name"}` is a compound index that spans multiple columns. — EpicPandaForce, Feb 27 '19 at 04:45

Bilal · Accepted Answer · 2021-03-10T06:03:34.343

Bit late on the answer. but hope so it will help someone.

Q1: What is the use of indices and @Index?

Indices: Can contain the list of indices on the table. whereas @Index is used to define an index.

For example:

@Entity(indices = {
    @Index("FirstIndex"),@Index(value="last_name",unique = true),
    @Index("SecondIndex"),@Index(value="first_name", unique = true)
})

As you can see, i have defined two indexes in this example which are separated by coma. Here "FirstIndex" and "SecondIndex" are the name of indexes. If i don't define the name of indexes (as you have quote in your example) then Room will set it to the list of columns joined by '' and prefixed by "index${tableName}". So if you have a table with name "Foo" and with an index of {"bar", "baz"}, generated index name will be "index_Foo_bar_baz".

Q2: What is the difference between @Index("name") and @Index(value = {"last_name", "address"})?

@index("name") //syntax to give name of index.
@index(value=last_name", "address") //Syntax to define, which column(s) need to be indexed

This is not true: "If i don't define the name of indexes (as you have quote in your example) then Room will consider it as an empty string." According to documentation: If not set, Room will set it to the list of columns joined by '_' and prefixed by "index_${tableName}". So if you have a table with name "Foo" and with an index of {"bar", "baz"}, generated index name will be "index_Foo_bar_baz" Source: https://developer.android.com/reference/androidx/room — Fer B., Dec 16 '20 at 18:37
@FerB. I have updated the answer. Thanks for the correction. — Bilal, Mar 10 '21 at 06:04

score 3 · Answer 2 · answered Mar 21 '22 at 07:24

In Kotlin actually the syntax is a bit different:

@Entity(indices = [Index(value = ["last_name", "address"])])
data class User(
    @PrimaryKey val id: Int,
    val firstName: String?,
    val address: String?,
    @ColumnInfo(name = "last_name") val lastName: String?,
    @Ignore val picture: Bitmap?
)

This is the Room Reference document with this and lots of other info: RoomDb Reference

score 2 · Answer 3 · answered Aug 29 '19 at 03:28

2

Indexing is the process of adding indexes which are used to quickly locate data without having to search every row in a database table every time the database table is queried or accessed. Room supports indexing certain fields or indexing group of fields using indices property to speed up the queries. you can check the below link for further knowledge.

https://proandroiddev.com/exploring-room-architecture-component-the-extras-cf3f0259ceed

answered Aug 29 '19 at 03:28

Vignesh R

125
8

3

Downvoted because does answer none of the 2 OP questions and explains a question not answered. Everybody knows what an index is and what's used for and any web search will give sufficient material to know about it, but the OP questions are very specific and they are not found with a simple search. I came here looking for an answer for the same questions, but didn't found it. – Fer B. Dec 16 '20 at 18:20

Room database: What is Index specific columns (indices and @Index) and how to use it?

3 Answers3