Jonas Sandman wrote: > 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?
For this particular query, and for one particular way of executing this query, this index can be used. Whether this query plan is good is difficult to say without knowing more about your data. Realize that the order of fields in the index is important. Your index on animals(typeid, name, subspecies) can be used for a condition like "typeid=?" or "typeid=? and name=?" or "typeid=? and name=? and subspecies=?" (in any order - AND operation is commutative), but it's useless when trying to satisfy, say, "name=?". Consider a traditional index at the end of a textbook, with terms arranged alphabetically. Such an index allows you to quickly find all terms beginning with 'a' or 'ab' or 'abc', but it's useless if you want to find all terms whose second letter is 'a'. An index in a relational database works the same way. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
