On Wed, May 7, 2014 at 6:58 PM, Hinrichsen, John <jhinrich...@c10p.com>wrote:
> On Wed, May 7, 2014 at 5:21 PM, Richard Hipp <d...@sqlite.org> wrote: > > > > > Do you have a database file where the 3.8.4.3 query plan really is > slower? > > Can you please run ANALYZE on that database and send us the content of > the > > "sqlite_stat1" table? > > > > > It is true that if we add the analyze, the query does use the automatic > covering index. The analyze wasn't necessary with sqlite-3.7.17. > The query planner in 3.7.17 was not nearly as clever as the 3.8.0+ query planner. It got the right answer given wrong information by dumb luck. See http://www.sqlite.org/queryplanner-ng.html and especially http://www.sqlite.org/queryplanner-ng.html#howtofix for further information. Also, it is generally considered good practice to create sufficient indices to avoid having to use an automatic index. Using an automatic index will make a two-way join O(NlogN). That's better than the O(N*N) that would occur without the automatic index, but you could have O(logN) if an appropriate persistent index is available. I know that there may arise cases where the query is sufficiently infrequent and the size of the necessary index is sufficiently large, that you may want to deliberately make use of a transient automatic index. But those cases are rare. SQLite comes with instrumentation (specifically the SQLITE_STMTSTATUS_AUTOINDEX verb for sqlite3_stmt_status() - http://www.sqlite.org/c3ref/stmt_status.html) that can be used to detect when automatic indices are used and alert the developer through a back channel to this fact so that she can fix the problem with an appropriate CREATE INDEX. In other words, SQLite provides you with the tools to help you detect and eliminate the use of automatic indices. Just saying.... -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
