These are all good points. Have you considered implementing hash joins for tables that join on columns that are not indexed? Typical hash joins (using the equality operator) can be performed in O(N) time without indexes. Because hash joins evaluate each row just once, they might also permit us to make calls to scalar functions more efficiently within the context of the join.
On Wed, May 7, 2014 at 8:30 PM, Richard Hipp <d...@sqlite.org> wrote: > 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 > -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
