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?
Jonas On Thu, Nov 26, 2009 at 4:42 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
