$ ./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]
-----------------------------------------------------------------------------
