Author: rhuijben
Date: Tue Mar 22 12:37:57 2011
New Revision: 1084147
URL: http://svn.apache.org/viewvc?rev=1084147&view=rev
Log:
Rewrite view to use a subquery instead of a join, which should avoid creating
a temporary table containing every row in NODES when the view is used in a
query. It also removes several unused columns from the view's result.
Note that view changes aren't applied to existing working copies; only to new.
* subversion/libsvn_wc/wc-metadata.sql
(NODES_CURRENT): Rewrite query to use a subquery per row instead of a join,
to allow SQLite to optimize a bit more over the table in the view.
Modified:
subversion/trunk/subversion/libsvn_wc/wc-metadata.sql
Modified: subversion/trunk/subversion/libsvn_wc/wc-metadata.sql
URL:
http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_wc/wc-metadata.sql?rev=1084147&r1=1084146&r2=1084147&view=diff
==============================================================================
--- subversion/trunk/subversion/libsvn_wc/wc-metadata.sql (original)
+++ subversion/trunk/subversion/libsvn_wc/wc-metadata.sql Tue Mar 22 12:37:57
2011
@@ -489,12 +489,10 @@ CREATE INDEX I_NODES_PARENT ON NODES (wc
current view.
*/
CREATE VIEW NODES_CURRENT AS
- SELECT * FROM nodes
- JOIN (SELECT wc_id, local_relpath, MAX(op_depth) AS op_depth FROM nodes
- GROUP BY wc_id, local_relpath) AS filter
- ON nodes.wc_id = filter.wc_id
- AND nodes.local_relpath = filter.local_relpath
- AND nodes.op_depth = filter.op_depth;
+ SELECT * FROM nodes AS n
+ WHERE op_depth = (SELECT MAX(op_depth) FROM nodes AS n2
+ WHERE n2.wc_id = n.wc_id
+ AND n2.local_relpath = n.local_relpath)
/* Many queries have to filter the nodes table to pick only that version
of each node with the base (least "current") op_depth. This view
