Why SQLite index does not speed up my query

Question

I have 2 tables:

tblValidItems - | - tblItems

validID itemID ------- ------ 3 1 5 2 6 3 ... 4 ~ 8 K items 5 ..... ~ 20 K items 

My request is to select certain things in tblItems , which are also in tblValidItems :

 SELECT tblItems.itemID FROM tblItems JOIN tblValidItems ON tblItems.itemID = tblValidItems.validID 

I tried a query with and without an index in both tables, but the results varied slightly:

• With indices on both tables - 127 ms
• Without index in any table - 132 ms

This surprised me because I thought the index would dramatically affect the speed of this query. Why is this not so?

Answer 1

Sqlite primary keys are indexed by default. You still join the indexed primary key.

Whenever you doubt how sqlite will work with your query, use the EXPLANATION REQUEST PLAN

Answer 2

I assume that the query is dominated by the time to return 8000 values, and not the time to find the rows.

Indexes are most useful when you reduce the size of the data you are working with. The reduction from 20k lines to 8k is not particularly noticeable.