I see! Doesn't that mean that my original suggestion is correct then?
create index idx_animals on animals(typeid, name, subspecies) as those three columns of the animals table are used in the select? Regards, Jonas On Thu, Nov 26, 2009 at 4:05 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > Jonas Sandman wrote: >> But I guess the answer is that only the "where" parts should be >> indexed, not the id's in the joins? > > No, that's generally not true. ON clauses in joins are basically a syntactic > sugar (though there's a subtle difference in case of outer joins). These > three statements are equivalent: > > select * from animals a > join animaltype at on at.id=a.typeid > where a.name='Monkey' and a.subspecies=2; > > select * from animals a, animaltype at > where a.name='Monkey' and a.subspecies=2 and at.id=a.typeid; > > select * from animals a join animaltype at on ( > at.id=a.typeid and a.name='Monkey' and a.subspecies=2); > > So, whether a particular condition is in WHERE clause or in ON clause is > immaterial. > > 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
