Why does an SQLite index 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 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:

With a indexes on both tables - 127ms
With no index on either table - 132ms

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

the Engine encounters overhead if the IDs allow NULLS. In addition if they are not of the same data type and the engine has to do an implicit conversion the index may not be used. If statistics are off the indexes may not be used as well (say you created the index then inserted the 8k valid items) Lots of reasons why an optimizer may choose not to use the index. — xQbert
– xQbert, Commented May 24, 2013 at 13:59

Gordon Linoff · Accepted Answer · 2013-05-24 13:57:45Z

5

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.

answered May 24, 2013 at 13:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dariusz · Accepted Answer · 2013-05-24 13:56:39Z

4

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

answered May 24, 2013 at 13:56

Dariusz

22.5k9 gold badges82 silver badges116 bronze badges

Collectives™ on Stack Overflow

Why does an SQLite index not speed up my query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related