Silverstripe 2.3.7 DB Cleanup

20100405

In silverstripe 2.3.7 mySQL db:

SitetreeVersion is 66MB.  Need to get it down..

Before cleaning old page versions (You will have no page edit history after this work..) save or delete any unpublished pages.
In mySQL if this query is not empty you have unpublished pages:

SELECT * 
FROM `SiteTree`
WHERE CONCAT_WS( '_', ID, Version ) NOT
IN (

SELECT CONCAT_WS( '_', id, Version ) AS combined_id
FROM SiteTree_Live
)

Because of an issue I had the lst time I did this (Orphaned pages) I also run this (should have no results)

SELECT * from SiteTree  where ID in

(
SELECT ParentID FROM SiteTree WHERE CONCAT_WS('_', ID, Version) NOT IN
(
SELECT CONCAT_WS('_', id, Version) AS combined_id FROM SiteTree_Live
)
)

Finally run the delete:

DELETE FROM SiteTree_versions
WHERE CONCAT_WS('_', RecordID, Version) NOT IN
(SELECT CONCAT_WS('_', id, Version) AS combined_id FROM SiteTree)

 

Then empty PageView and optimise all tables.

Yay, Database backup down from 55MB to 5MB.