> -----Original Message-----
> From: C. Michael Pilato [mailto:cmpil...@collab.net]
> Sent: maandag 25 juni 2012 20:09
> To: dev@subversion.apache.org
> Cc: Bert Huijben; comm...@subversion.apache.org
> Subject: Re: svn commit: r1353676 - in /subversion/trunk/subversion:
> include/private/svn_wc_private.h include/svn_ra.h libsvn_client/ra.c
> libsvn_ra_serf/update.c libsvn_wc/adm_ops.c
>
> On 06/25/2012 02:04 PM, Bert Huijben wrote:
> > If we want to enable this code in 1.8 we should add an index on the
> > md5_checksum in the pristine table with the format bump for 1.8.
> >
> > Currently the md5 lookup performs a table scan (but is only used from the
> > deprecated libsvn_wc commit logic). For that 1.5 million file working
> > copy reported today on users@s.a.o, this code might be much slower than
> > just downloading the files again without an index.
>
> Ewww... yeah, table scan is no good. So what's involved in adding,
> populating, and maintaining such a new index?
Something like:
(I only tested the queries with the query test tool, not via the test suite)
[[
Following up on r1353676, introduce an index on the md5 checksum in the pristine
table.
* subversion/tests/libsvn_wc/wc-queries-test.c
(slow_statements): Remove STMT_SELECT_PRISTINE_BY_MD5, as this now uses an
index.
* subversion/tests/libsvn_wc/wc-queries-test.c
(STMT_CREATE_SCHEMA): Add new index for new wc.db instances.
(STMT_UPGRADE_TO_30): Add new index if it doesn't exist yet on the future
bump to format 30.
]]
Index: subversion/tests/libsvn_wc/wc-queries-test.c
===================================================================
--- subversion/tests/libsvn_wc/wc-queries-test.c (revision 1353631)
+++ subversion/tests/libsvn_wc/wc-queries-test.c (working copy)
@@ -88,9 +88,6 @@
STMT_LOOK_FOR_WORK,
STMT_HAS_WORKING_NODES,
- /* Need index? */
- STMT_SELECT_PRISTINE_BY_MD5, /* Only used by deprecated api */
-
/* Full temporary table read */
STMT_INSERT_ACTUAL_EMPTIES,
STMT_SELECT_REVERT_LIST_RECURSIVE,
Index: subversion/libsvn_wc/wc-metadata.sql
===================================================================
--- subversion/libsvn_wc/wc-metadata.sql (revision 1353631)
+++ subversion/libsvn_wc/wc-metadata.sql (working copy)
@@ -107,7 +107,8 @@
md5_checksum TEXT NOT NULL
);
-
+CREATE INDEX I_PRISTINE_MD5 ON PRISTINE (md5_checksum);
+
/* ------------------------------------------------------------------------- */
/* The ACTUAL_NODE table describes text changes and property changes
@@ -778,6 +779,8 @@
CREATE UNIQUE INDEX IF NOT EXISTS I_NODES_MOVED
ON NODES (wc_id, moved_to, op_depth);
+CREATE INDEX IF NOT EXISTS I_PRISTINE_MD5 ON PRISTINE (md5_checksum);
+
/* Just to be sure clear out file external skels from pre 1.7.0 development
working copies that were never updated by 1.7.0+ style clients */
UPDATE nodes SET file_external=1 WHERE file_external IS NOT NULL;
-----
Bert
