Okay, thanks! I think I understand better now, how to proceed. Regards, Jonas
On Fri, Nov 27, 2009 at 3:19 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > Jonas Sandman wrote: >> Yes, but considering that I first join on typeid and then have name >> and subspecies in the where, wouldn't that index be optimal for that >> query? > > Again, the order of conditionals in the query is largely irrelevant. > Remember, your statement is equivalent to this one: > > select * from animals a, animaltype at > where a.name='Monkey' and a.subspecies=2 and at.id=a.typeid; > > In this form, it should be obvious that conditions can be evaluated in any > order. > > If I had to guess without knowing the composition of your data, I'd say that > the most efficient plan would involve finding all records in animals with > a.name='Monkey' and a.subspecies=2, and for each such record, find matching > records in animaltype. I'm assuming that animals table contains records about > many different animals, and the number of records about monkeys is small > compared to the total number of records; in other words, that the condition > "a.name='Monkey' and a.subspecies=2" is highly selective. > > For this query plan, you would want indexes on animals(name, subspecies) and > animaltype(id). > > 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
