警告是因为PlayerID
不是构造索引(PrimaryKey)的主要依据.也就是说,索引、主键将首先使用TournamentId
构建,然后使用PlayerId
构建.
- 也就是说,这只是一个警告,而且"不是索引的一部分"也不是对问题的准确描述.
因此,基于PlayerId
的表搜索必须扫描rowid或主键才能找到有问题的PlayerId
.
- 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.个
要删除警告,只需使用:-
@ColumnInfo(index = true)
var playerId:Long
然后,PlayerId
上的指数将存在并得到维护.如果是,则可以通过索引获得playerID的相应行.
- 请注意,在插入/删除和可能更新时维护附加索引会产生开销,但在查询时会有潜在的yield .
Demonstration (using SQLite tool)个
请考虑以下事项:
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;
这是:-
- creates some 250000 rows with every combination of t (TRN1 - TRN500) and p (pLY1 - PLY500).
- 不需要理解如何
- 消息
> Affected rows: 250000
和> Time: 0.833s
- Asks for an explanation of what the the query
SELECT * FROM pe WHERE p='PLY1'
个 does.
- Output is i.e. A FULL scan of the table
- 运行查询,重要的是日志(log)中的消息是:-
SELECT * FROM pe WHERE p= 'PLY1'
个
> OK
个个
- 102
- 在p列上创建索引.
- Explains the same query BUT NOW:-
- Output is i.e the pe_indexon_p_column will be used.
- Runs the EXACT same query, now the messages to the log are:-
SELECT * FROM pe WHERE p='PLY1'
个
> OK
个个
- 100
0.012 seconds is noticeably less than the FULL SCAN that took 0.032 seconds个