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
