Re: [sqlite] Foreign key vs index (continue)

On 11/13/2013 11:14 PM, Igor Korot wrote: But then in order to speed up this query I need to create an index leaguescorehitter(scoreid), right? I don't know. My crystal ball is cloudy lately, I can't quite make out your database schema from here. -- Igor Tandetnik

Re: [sqlite] Foreign key vs index (continue)

Igor, On Wed, Nov 13, 2013 at 7:34 PM, Igor Tandetnik wrote: > On 11/13/2013 10:13 PM, Igor Korot wrote: >> >> What I don't understand is: >> >> All fields in WHERE clause are declared as primary/foreign keys. And >> it still gives full table scan on the first iteration. > >

Re: [sqlite] Foreign key vs index (continue)

>What I don't understand is: > >All fields in WHERE clause are declared as primary/foreign keys. And >it still gives full table scan on the first iteration. > Foreign Key declarations specify referential integrity constraints. Primary Key declarations necessarily create an index to enforce

Re: [sqlite] Foreign key vs index (continue)

On 11/13/2013 10:13 PM, Igor Korot wrote: What I don't understand is: All fields in WHERE clause are declared as primary/foreign keys. And it still gives full table scan on the first iteration. Defining a foreign key doesn't create any indexes, and doesn't in any way affect the behavior of

[sqlite] Foreign key vs index (continue)

Hi, ALL, Now that the loop in the query is fixed I want to come back to this topic. Here is modified query plan: sqlite> EXPLAIN QUERY PLAN SELECT playersinleague.playerid,scorehits.scorename,l eaguescorehitter.value FROM playersinleague,scorehits,leaguescorehitter WHERE sc orehits.scoreid =