On Thu, Jun 21, 2012 at 08:30:31AM -0400, Richard Hipp scratched on the wall: > On Thu, Jun 21, 2012 at 1:47 AM, nn6eumtr <[email protected]> wrote: > > > > > If I have a table: > > > > create table tmp ( > > x integer, > > y integer > > ) > > > > And a an index with compound key: > > > > create unique index idx on tmp (x, y); > > > > And I did a query: > > > > select * from tmp where x = 9 > > > > Would sqlite know how to use the index to find records where x = 9? Or > > would it opt for a scan instead? > > > > It will use the index.
I'm likely setting myself up for Richard to tell me I'm wrong, but.... -If the table has more columns (i.e. columns that are not in the index). -And you ask for them all (SELECT * ...). -And you've run ANALYZE. -And the index has poor diversity. It might be possible that SQLite will choose to do a full scan (?). I think. In this case, since the index has all the values you're asking for, SQLite should always use the index. This is because SQLite can extract all the required values directly from the index itself, and will never touch the main table record. This is more efficient, even if the index is known to have poor diversity. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
