Why is this (1,44 seconds)
select * from movies m
where m.movieid in (select mp.movieid from people p, moviepeople mp
where p.fullname="Spencer, Bud" and mp.personid=p.personid)
much faster than the following (Runtime several minutes!):
select * from movies m
join moviepeople mp on mp.movieid=m.movieid
join people p on p.personid=mp.personid
where p.fullname="Spencer, Bud"
Shouldn't it perform equally?
This is my table structure:
CREATE TABLE moviepeople (
movieid integer NOT NULL,
personid integer NOT NULL,
);
CREATE TABLE people (
personid integer primary key NOT NULL,
fullname character varying(200) NOT NULL,
);
CREATE TABLE movies (
movieid integer primary key NOT NULL,
moviename character varying(300) NOT NULL,
);
create unique index if not exists movies_moviename_key on movies(moviename);
create unique index if not exists people_fullname_key on people(fullname);
create unique index if not exists people_personid_key on
people(personid); // is this duplicate because this is already primary key?
create unique index if not exists movies_movieid_key on movies(movieid);
create index if not exists moviepeople_movieid_idx on moviepeople (movieid);
create index if not exists moviepeople_personid_idx on moviepeople
(personid);
The database was currently vacuumed and analyzed.
--
Fun stuff, Java and C# programming, freeware games, applications and tools
www.deutronium.de.vu | www.deutronium.de.ki
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
