CODEWITHSUNDEEP
sqlsqlite
Robert
Robert

Reputation: 38213

Why does an SQLite index not speed up my query

I have 2 tables:

tblValidItems - | - tblItems

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

My query is to select certain things in tblItems that are also in tblValidItems:

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

I tried the query with and without an index on both tables, but the results varied vary little:

This surprised me because I thought an index would dramatically effect the speed of this query. Why dons't it?

Upvotes: 4

Views: 452

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I am guessing that the query is dominated by the time to return the 8,000 values and not the time to find the rows.

Indexes are most useful when you are reducing the size of the data you are working with. The reduction from 20k rows to 8k is not particularly signficant.

Upvotes: 5

Dariusz
Dariusz

Reputation: 22241

Sqlite primary keys are indexed by default. You are joining on an indexed primary key anyway.

Whenever you have doubts on how sqlite will work with your query, use EXPLAIN QUERY PLAN

Upvotes: 4

Related Questions