Foreign key is part of primary key but it is not part of an index. Room

Question

I have a table participant to connect tables tournaments and players. For this, I write :

@Entity(tableName = "participants", foreignKeys = [
ForeignKey(entity = TournamentEntity::class,
    parentColumns = ["id"],
    childColumns = ["tournamentId"],
    onDelete = ForeignKey.CASCADE),
ForeignKey(entity = PlayerEntity::class,
    parentColumns = ["id"],
    childColumns = ["playerId"],
    onDelete = ForeignKey.CASCADE),
], primaryKeys = ["tournamentId","playerId"])
data class ParticipantEntity(
var tournamentId:Long,
var playerId:Long
)

But during the build, I see the warning :

playerId column references a foreign key but it is not part of an index. This may trigger full table scans whenever parent table is modified so you are highly advised to create an index that covers this column.

Normally, when a foreign key is a part of the primary key, it isn't necessary to create an index.

Please, what is the procedure to follow to fix this issue?

Answer 1

The WARNING is because the PlayerID is not what the index (PrimaryKey) is primarily constructed from. That is the index, the primary key, will be built using first the TournamentId then the PlayerId.

• i.e. it is only a warning and also "not part of an index" is not a precise description of the issue.

So a search of the table base upon a PlayerId would either have to scan the rowid or the primarykey to find the PlayerId in question.

• as Room with few exceptions requires the use of a rowid table, there is always an index on the normally hidden rowid column. This can be up to twice as fast to scan than other indexes, so may be the index used as decided by the query optimiser.

To remove the warning, simply use:-

@ColumnInfo(index = true)
var playerId:Long

Then an index on the PlayerId will exist and be maintained. If so then the respective row can be obtained for a playerId via the index.

• note that there is an overhead in maintaining the additional index when inserting/deleting and perhaps updating BUT there is the potential gain when querying.

Demonstration (using SQLite tool)

Consider the following:-

DROP TABLE IF EXISTS pe;
CREATE TABLE IF NOT EXISTS pe (t TEXT, p TEXT, PRIMARY KEY(t,p));
/* add some data generated recursively (no need to understand just that 250000 rows are added)*/
WITH
    cte_counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte_counter LIMIT 500),
    cte_t(t_name) AS (SELECT 'TRN'||n FROM cte_counter),
    cte_p(p_name) AS (SELECT 'PLY'||n FROM cte_counter)
INSERT INTO pe SELECT t_name,p_name FROM cte_t JOIN cte_p;

/* Demonstrate access WITHOUT an index on the p column */
EXPLAIN QUERY PLAN
SELECT * FROM pe WHERE p='PLY1';
SELECT * FROM pe WHERE p= 'PLY1';
/* Add the index on the p column aka @ColumnInfo(index = true) */
CREATE INDEX pe_idxon_p_column ON pe(p);
/* Demonstrate access WITHOUT an index on the p column */
EXPLAIN QUERY PLAN
SELECT * FROM pe WHERE p='PLY1';
SELECT * FROM pe WHERE p='PLY1';
/* Cleanup demo environment */
DROP TABLE IF EXISTS pe;

This:-

1. creates some 250000 rows with every combination of t (TRN1 - TRN500) and p (pLY1 - PLY500).
   1. no need to understand how
   2. message > Affected rows: 250000 and > Time: 0.833s
2. Asks for an explanation of what the the query SELECT * FROM pe WHERE p='PLY1' does.
   1. Output is i.e. A FULL scan of the table
3. Runs the query, importantly the messages to the log are:-
   1. SELECT * FROM pe WHERE p= 'PLY1'
   2. > OK
   3. > Time: 0.032s
4. Creates an index on the p column.
5. Explains the same query BUT NOW:-
   1. Output is i.e the pe_indexon_p_column will be used.
6. Runs the EXACT same query, now the messages to the log are:-
   1. SELECT * FROM pe WHERE p='PLY1'
   2. > OK
   3. > Time: 0.012s

0.012 seconds is noticeably less than the FULL SCAN that took 0.032 seconds