A compile-time warning in the Android Room about a column in a foreign key is not part of the index. What does it mean?

Question

I am using the Android Persistence Library from the Android Architecture components recently announced by Google I / O. Everything seems to work, but I get the following error:

Warning: the tagId column refers to a foreign key, but it is not part of the index. This can cause a full table scan when the parent table is therefore highly recommended that you create an index that spans this column.

My database has 3 tables: Note , Tag and JoinNotesTags . Tag notes are many-to-many relationships, so the JoinNotesTags table handles the display. The tables are simple:

• Note.id and Tag.id are primary keys
• JoinNotesTags.noteId Links
• JoinNotesTags.tagId Link Tag.id

Foreign key constraints are defined in the JoinNotesTags table. For reference, here is the CREATE TABLE statement for the JoinNotesTags table:

 "CREATE TABLE IF NOT EXISTS `JoinNotesTags` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `noteId` INTEGER, `tagId` INTEGER, FOREIGN KEY(`noteId`) REFERENCES `Note`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE , FOREIGN KEY(`tagId`) REFERENCES `Tag`(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION )" 

And here is the corresponding @Entity annotation for this class:

 @Entity( indices = arrayOf(Index(value = *arrayOf("noteId", "tagId"), unique = true)), foreignKeys = arrayOf( ForeignKey( entity = Note::class, parentColumns = arrayOf("id"), childColumns = arrayOf("noteId"), onDelete = ForeignKey.CASCADE), ForeignKey( entity = Tag::class, parentColumns = arrayOf("id"), childColumns = arrayOf("tagId")) ) ) 

As you can see from the @Entity annotation, tagId included in the composite unique index along with noteId . I confirmed that this index is also correctly defined in the json auto-generate file:

 "CREATE UNIQUE INDEX `index_JoinNotesTags_noteId_tagId` ON `JoinNotesTags` (`noteId`, `tagId`)" 

So my question is: is this warning just a mistake in the room library (still alpha release), i.e. during analysis during compilation there is no fact that tagId is part of this composite index? Or do I really have an indexing problem that I need to solve in order to avoid a full table scan?

Answer 1

When changing the Tag table for the database, you may need to find the corresponding rows in the JoinNotesTags table. To be effective, an index in the tagId column is tagId .

Your composite index is not suitable for this; due to the way indexes work , the column (s) to be searched must be the leftmost column in the index.

You should add an index only to the tagId column. (You can change the order of the columns in the composite index, but then you will have the same problem with noteId .)

Answer 2

You need to add an index to the columns for faster queries. Here is an example

 @Entity(indices = {@Index("artist_id")}) public class Artist{ @NonNull @PrimaryKey @ColumnInfo(name = "artist_id") public String id; public String name; } 

Answer 3

when in kotlin code:

before

 @Expose @SerializedName("question_id") @ColumnInfo(name = "question_id") var questionId: Long 

after

 @Expose @SerializedName("question_id") @ColumnInfo(name = "question_id", index = true) //just add index = true var questionId: Long