On Fri, Aug 30, 2013 at 2:44 PM, <[email protected]> wrote: > > The query > [[ > DELETE FROM nodes WHERE wc_id = ?1 AND (local_relpath = ?2 OR > (((local_relpath) > (CASE (?2) WHEN '' THEN '' ELSE (?2) || '/' END)) AND > ((local_relpath) < CASE (?2) WHEN '' THEN X'FFFF' ELSE (?2) || '0' END))) > AND op_depth = ?3 > ]] >
> Is handled by 3.7.17 as: > 0|0|0|SEARCH TABLE nodes USING COVERING INDEX sqlite_autoindex_NODES_1 > (wc_id=? AND local_relpath=? AND op_depth=?) (~1 rows) > 0|0|0|SEARCH TABLE nodes USING COVERING INDEX sqlite_autoindex_NODES_1 > (wc_id=? AND local_relpath>? AND local_relpath<?) (~1 rows) > > Which I read as two separate operations, under the 'OR' optimization > > But 3.8.0.1 does: > 0|0|0|SEARCH TABLE nodes USING COVERING INDEX I_NODES_PARENT (wc_id=?) > > Which in our case is far worse than using the primary key on the normal > table as wc_id is constant and local_relpath +- our primary key. > > But the query planner has know way of knowing that wc_id is always the same value, unless you run ANALYZE. Can you do that, please: Run ANALYZE, then send in the content of the resulting "sqlite_stat1" table. -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
