$ ./sqlite3.exe v.db vacuum
ATTACH 'C:\TMP\etilqs_SOVEJE7Rni84Zzy' AS vacuum_db;
PRAGMA vacuum_db.synchronous=OFF
BEGIN EXCLUSIVE;
CREATE TABLE vacuum_db.t1(a, b, primary key(b, a))
CREATE TABLE vacuum_db.t2(c, d)
CREATE INDEX vacuum_db.t2i on t2(d, c)
INSERT INTO vacuum_db.'t1' SELECT * FROM 't1';
INSERT INTO vacuum_db.'t2' SELECT * FROM 't2';
INSERT
INTO vacuum_db.sqlite_master   SELECT type, name, tbl_name, rootpage,
sql    FROM sqlite_master   WHERE type='view' OR type='trigger'      OR
(type='table' AND rootpage=0)



Strange, I thought INDEXes were created after the INSERTs in a VACUUM.

If this were the case, perhaps the resultant index pages in the newly vacuumed 
database would be closer together and would run quicker in a cold disk cache 
situation.

Is this untested patch worth a try?



Index: src/vacuum.c
===================================================================
RCS file: /sqlite/sqlite/src/vacuum.c,v
retrieving revision 1.65
diff -u -3 -p -r1.65 vacuum.c
--- src/vacuum.c        18 Nov 2006 20:20:22 -0000      1.65
+++ src/vacuum.c        18 Nov 2006 21:14:14 -0000
@@ -143,14 +143,6 @@ int sqlite3RunVacuum(char **pzErrMsg, sq
       "   AND rootpage>0"
   );
   if( rc!=SQLITE_OK ) goto end_of_vacuum;
-  rc = execExecSql(db, 
-      "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14,100000000)"
-      "  FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' ");
-  if( rc!=SQLITE_OK ) goto end_of_vacuum;
-  rc = execExecSql(db, 
-      "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21,100000000) "
-      "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");
-  if( rc!=SQLITE_OK ) goto end_of_vacuum;
 
   /* Loop through the tables in the main database. For each, do
   ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy
@@ -166,6 +158,18 @@ int sqlite3RunVacuum(char **pzErrMsg, sq
   );
   if( rc!=SQLITE_OK ) goto end_of_vacuum;
 
+  /* Create indexes after the INSERTs to keep their pages closer
+  ** together.
+  */
+  rc = execExecSql(db, 
+      "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14,100000000)"
+      "  FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' ");
+  if( rc!=SQLITE_OK ) goto end_of_vacuum;
+  rc = execExecSql(db, 
+      "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21,100000000) "
+      "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");
+  if( rc!=SQLITE_OK ) goto end_of_vacuum;
+
   /* Copy over the sequence table
   */
   rc = execExecSql(db, 





 
____________________________________________________________________________________
Sponsored Link

Mortgage rates near 39yr lows. 
$310k for $999/mo. Calculate new payment! 
www.LowerMyBills.com/lre

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------

Reply via email to