Leaving out the extra fields the movie table, enforcing referential integrity,
and using "same column names" to hold the same data, and declaring the right
indexes, you can get a much more efficient solution, both in the expression and
the execution:
These optimizations are only necessary *after* you have solved the problem, and
then really only if performance is inadequate. Enforcement of referential
integrity and ensuring good indexes should always be done, of course.
CREATE TABLE movie
(
movieid integer primary key,
title text not null unique collate nocase
);
CREATE TABLE actor
(
actorid integer primary key,
name text not null unique collate nocase
);
CREATE TABLE casting
(
movieid integer not null references movie,
ord integer not null,
actorid integer not null references actor,
primary key(movieid, ord, actorid),
unique (actorid, movieid, ord)
) WITHOUT ROWID;
select title, name
from movie natural join casting natural join actor
where ord = 1
and movieid in (select movieid
from casting natural join actor
where name = 'Julie Andrews');
** note that the subquery for the IN list does not need to contain the movie
table at all since the movieid is in the casting table **
sqlite> .explain
sqlite> explain query plan
...> select title, name
...> from movie natural join casting natural join actor
...> where ord = 1
...> and movieid in (select movieid
...> from casting natural join actor
...> where name = 'Julie Andrews');
SELECT item[0] = {0:1}
item[1] = {2:1}
FROM {0,*} = movie
{1,*} = casting
{2,*} = actor
WHERE AND(AND(AND(EQ({1:1},1),IN({0:-1},SELECT {3:0}
FROM {3,*} = casting
{4,*} = actor
WHERE AND(EQ({4:1},'Julie
Andrews'),EQ({3:2},{4:-1}))
END)),EQ({0:-1},{1:0})),EQ({1:2},{2:-1}))
END
sele order from deta
---- ------------- ---- ----
0 0 0 SEARCH TABLE movie USING INTEGER PRIMARY KEY
(rowid=?)
0 0 0 EXECUTE LIST SUBQUERY 1
1 0 1 SEARCH TABLE actor USING COVERING INDEX
sqlite_autoindex_actor_1 (name=?)
1 1 0 SEARCH TABLE casting USING COVERING INDEX
sqlite_autoindex_casting_2 (actorid=?)
0 1 1 SEARCH TABLE casting USING PRIMARY KEY (movieid=?
AND ord=?)
0 2 2 SEARCH TABLE actor USING INTEGER PRIMARY KEY
(rowid=?)
Even more efficiently you could flatten the query thus:
select m.title, a.name
from movie as m, casting as c, actor as a, casting as c2, actor as a2
where a2.name = 'Julie Andrews'
and c2.actorid = a2.actorid
and m.movieid = c2.movieid
and c.movieid = c2.movieid
and c.ord = 1
and a.actorid = c.actorid;
which finds the appropriate actor row, uses that to retrieve all the casting
rows which in turn leads to all the movie rows. This row set is then joined
back through (another instance of the same) casting table with a specific ord
given to get the lead actor name (another instance of the same actor table).
The actor and casting table are used twice, so they have to be referenced twice
with different aliases.
sqlite> explain query plan
...> select m.title, a.name
...> from movie as m, casting as c, actor as a, casting as c2, actor as a2
...> where a2.name = 'Julie Andrews'
...> and c2.actorid = a2.actorid
...> and m.movieid = c2.movieid
...> and c.movieid = m.movieid
...> and c.ord = 1
...> and a.actorid = c.actorid;
SELECT item[0] = {0:1}
item[1] = {2:1}
FROM {0,*} = movie (AS m)
{1,*} = casting (AS c)
{2,*} = actor (AS a)
{3,*} = casting (AS c2)
{4,*} = actor (AS a2)
WHERE AND(AND(AND(AND(AND(EQ({4:1},'Julie
Andrews'),EQ({3:2},{4:-1})),EQ({0:-1},{3:0})),EQ({1:0},{3:0})),EQ({1:1},1)),EQ({2:-1},{1:2}))
END
sele order from deta
---- ------------- ---- ----
0 0 4 SEARCH TABLE actor AS a2 USING COVERING INDEX
sqlite_autoindex_actor_1 (name=?)
0 1 3 SEARCH TABLE casting AS c2 USING COVERING INDEX
sqlite_autoindex_casting_2 (actorid=?)
0 2 0 SEARCH TABLE movie AS m USING INTEGER PRIMARY KEY
(rowid=?)
0 3 1 SEARCH TABLE casting AS c USING PRIMARY KEY
(movieid=? AND ord=?)
0 4 2 SEARCH TABLE actor AS a USING INTEGER PRIMARY KEY
(rowid=?)
sqlite>
Unless there are many millions of rows in the tables both query forms are
probably equally efficient. This last form of the query is navigationally
constrained and specifically takes advantage of the "nested loops" method that
SQLite uses to actually perform the query and is somewhat implementation
dependent. Some SQL optimizers may arrive at the same end result given the
original simpler query, and some may have other solution methods that are more
efficient depending on how the query is phrased.
Theoretically, the plan used to execute the query (ie, obtain results) should
be independent of which of the many ways you could phrase the query. However,
almost no query optimizer is perfect and each database will have quirks that
you may need to use to phrase the query so that an optimum execution is
obtained. However, you really only want/need to do these optimizations after
arriving at a working "most direct" (or brute force) translation in order to
remove obvious inefficiency and perhaps coax maximum performance from the
solution.
>-----Original Message-----
>From: Errol Emden [mailto:eem...@hotmail.com]
>Sent: Friday, 8 August, 2014 16:20
>To: General Discussion of SQLite Database; kmedc...@dessus.com
>Subject: RE: [sqlite] What am I doing wrong?
>
>Keith...thanks vmuch for your insightful and enlightening approach of
>translating 'in parts'. I am truly indebted to you for your mentoring
>approach...be well.
>
>
>
>> Date: Fri, 8 Aug 2014 15:03:13 -0600
>> From: kmedc...@dessus.com
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] What am I doing wrong?
>>
>> >I am to list the film title and the leading actor for all of the films
>> >'Julie Andrews' played in.
>>
>> >The following is structure of the tables used:
>>
>> >movie(id, title, yr, director, budget, gross);
>> >actor(id, name);
>> >casting(movieid, actorid, ord).
>> >The column name ord has a value of 1 if the actor is in starring role.
>>
>> Translate the English in parts:
>>
>> "all the films "Julie Andrews" olayed in
>> ->
>> select movie.id
>> from movie, casting, actor
>> where movie.id = casting.movieid
>> and casting.actorid = actor.id
>> and actor.name = 'Julie Andrews'
>>
>> then wrap the part "list the film title and the leading actor" around
>that list of films:
>>
>> select movie.name, actor.name
>> from movie, casting, actor
>> where movie.id = casting.movieid
>> and casting.actorid = actor.id
>> and casting.org = 1
>> and movie.id in (select movie.id
>> from movie, casting, actor
>> where movie.id = casting.movieid
>> and casting.actorid = actor.id
>> and actor.name = 'Julie Andrews');
>>
>> Being able to translate problem statements from well formed human
>language into well formed computer language statement(s) "The Solution"
>is what separates "programmers" from "coders". The problem statement does
>not require any ordering of the results, so they are in visitation order
>(which is not random, but may appear to be random to "coders").
>>
>>
>>
>>
>>
>> _______________________________________________
>> 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
