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

[Igor Tandetnik]

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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Korot]

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.
>
>
> Defining a foreign key doesn't create any indexes, and doesn't in any way
> affect the behavior of SELECT statements...

But then in order to speed up this query I need to create an index
leaguescorehitter(scoreid), right?

Thank you.

>
>
>> Also is it checking conditions from left to right or right to left?
>
>
> Who is checking which conditions?
>
>
>> Meaning the first row in the plan indicate equality against
>> scorehits/scorepitch.scoreid, right?
>
>
> The first row in the plan is SCAN TABLE. It visits every row in the table,
> regardless of any equality of anything to anything else.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

>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 uniqueness (and so that 
the value can be used to find the row referenced by the key).  Similarly a 
UNIQUE constraint also creates an index to enforce uniqueness.  Query plan 
optimization is based on choosing the lowest cost nested loop order because 
*appropriate indexes exist* which can be used to find the data of interest more 
quickly than a sequential scan of the entire table looking for rows which 
satisfy the selection constraints (WHERE clauses) -- or which create visitation 
order that might partially satisfy an ORDER BY or GROUP BY ...

Have you declared useful indexes so that the data you are looking for can be 
found without having to resort to a table scan?  You need to manually create 
indexes on *both* the source and target of a foreign key if they are not 
otherwise created by a constraint which does create an index, such as "primary 
key" or "unique" constraints -- they are not created for you.

If you have created appropriate indexes, have you run the ANALYZE command to 
gather index distribution statistics for the query optimizer?




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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 SELECT statements.



Also is it checking conditions from left to right or right to left?


Who is checking which conditions?


Meaning the first row in the plan indicate equality against
scorehits/scorepitch.scoreid, right?


The first row in the plan is SCAN TABLE. It visits every row in the 
table, regardless of any equality of anything to anything else.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign key vs index (continue)

[Igor Korot]

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 = leaguescorehitter.scoreid AND playersinleague.playerid = leagu
escorehitter.playerid AND playersinleague.playerid = 1 AND playersinleague.id =
1 UNION ALL SELECT playersinleague.playerid,scorepitch.scorename,leaguescorepitc
her.value FROM playersinleague,scorepitch,leaguescorepitcher WHERE playersinleag
ue.playerid = leaguescorepitcher.playerid AND leaguescorepitcher.scoreid = score
pitch.scoreid AND playersinleague.playerid = 1 AND playersinleague.id = 1;
1|0|2|SCAN TABLE leaguescorehitter (~100 rows)
1|1|0|SEARCH TABLE playersinleague USING COVERING INDEX sqlite_autoindex_players
inleague_1 (id=? AND playerid=?) (~1 rows)
1|2|1|SEARCH TABLE scorehits USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
2|0|2|SCAN TABLE leaguescorepitcher (~100 rows)
2|1|0|SEARCH TABLE playersinleague USING COVERING INDEX sqlite_autoindex_players
inleague_1 (id=? AND playerid=?) (~1 rows)
2|2|1|SEARCH TABLE scorepitch USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

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.

Can someone please help to solve it?
Also is it checking conditions from left to right or right to left?
Meaning the first row in the plan indicate equality against
scorehits/scorepitch.scoreid, right?

Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users