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

Reply via email to