"Derek Scherger" <de...@echologic.com> wrote in message news:e97446630903082113x1efb0173mfde6aa70909b1...@mail.gmail.com > I'm curious though, why does 'select *' not also use the unique index > on the > same grounds that it could get all the values it needs from the index > and > avoid the table lookup?
It can't. "hash" column is not part of the index. > Would re-ordering our unique index to be (name, id, value, ...) > rather than (name, value, id, ...) allow a query with only name and > id to use it more > effectively? Yes. So would (id, name, ...) > I' wondering if the problem is that value is ahead of id > in the > index and we can't use anything past name because we don't have a > value, Correct. SQLite can use any prefix of the index, but as soon as you skip a column, the index is useless. Think about it this way. Consider an actual index at the end of a book - an alphabetical list of terms. You could use the index to quickly find all words that begin with A, or with AB. But it's useless if you need to find all words whose second letter is X. >> The id index is much >>> more selective that the unique index. >> >> You may know that, but SQLite doesn't. > > Fair enough. I wasn't sure how much sqlite might know about the > distribution > of actual data in the table and what its query optimizer/planner > might do > with such information. Try running ANALYZE command. Sometimes it helps the optimizer to make better decisions. But in general, SQLite's optimizer is less sophisticated than those found in "big" databases like DB2 and Oracle. Hence "lite" in SQLite. Without ANALYZE, SQlite has no information about distribution of values. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
