Hello! Over the last few weeks we were profiling our usage of SQLite. We found three common patterns that are not treated well by the optimizer. I believe at least two of them can be fixed easily and they are included below.
1) Consider the following schema: create table a (a, b, c, d); create index aIdx on a (a, d); Now the data in columns "b" and "c" can be pretty large. Let's make the following query: select a, d from a; Full-table scan is done even if an index exists that covers all the data required by the query. This is a very simplified example of a general problem where the query contains several conditions in the WHERE query, but none of them can be answered by lookup in the index. A covering index exists though and using it causes much less data to be loaded from the disk. explain query plan select a, d from a; 0|0|0|SCAN TABLE a (~1000000 rows) explain query plan select a, d from a where a >= 0 or a < 0; 0|0|0|SEARCH TABLE a USING COVERING INDEX aIdx (a>?) (~250000 rows) 0|0|0|SEARCH TABLE a USING COVERING INDEX aIdx (a<?) (~250000 rows) 2) Indices are not used for IS operator. create table a (a, b, c, d); create index aIdx on a (a, d); explain query plan select a from a where a IS 'test'; 0|0|TABLE a sqlite> explain query plan select a from a where a='test' AND a IS NOT NULL; 0|0|TABLE a WITH INDEX aIdx Best regards, Filip Navara _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
