Make your Drupal 7 faster by removing all revisions

Maybe you were in the same case as me. I got a big database full of revision rows in every revision table with their usefulnessofmeter beyond zero. In fact, I’m pretty sure revisions are not needed in 99.999% Drupal installation.

So, I tried to remove them by disabling the create a revision default option in my content type settings but it didn’t change anything. When you check the database manually, you can see every field has it’s own revision table. If you install the field_collection module, every collections and their fields have a revision table too. I got millions of revisions in some tables for just a few nodes. It was hell.

Here is how I first got rid off all unnecessary row in all revision tables with a little script

require_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

$nodes = db_select('node', 'n')
    ->fields('n', array('nid','vid'))
    ->execute();

foreach ($nodes as $node) {

	$revisions = db_select('node_revision', 'n')
		->fields('n', array('vid'))
		->condition('n.nid',$node->nid)
		->condition('n.vid',$node->vid, '<')
		->execute();

	foreach ($revisions as $rev) {
		node_revision_delete($rev->vid);    
	}
}

require_once './includes/bootstrap.inc';

drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

$nodes = db_select('node', 'n')

->fields('n', array('nid','vid'))

->execute();

foreach ($nodes as $node) {

$revisions = db_select('node_revision', 'n')

->fields('n', array('vid'))

->condition('n.nid',$node->nid)

->condition('n.vid',$node->vid, '<')

->execute();

foreach ($revisions as $rev) {

node_revision_delete($rev->vid);

}

This script will remove every single revision and keep the current one.

If you do it manually by processing the db with SQL, you could forget some rows or worse, destroy database or break important links somewhere. But sometimes you have to.

I had table revisions still remaining field_collection_item_revision and node_revision. I don’t know why but revisions were always created each time a field_collection_item was added to the node.

So I found a very cool module that replaces the storage engine for each field : field_sql_norevisions

Very easy to install. Enable it to replace default storage engine and execute a little SQL query to replace all existing ones.

UPDATE `field_config` SET `storage_type`= 'field_sql_norevisions',
`storage_module`= 'field_sql_norevisions'

1 2	UPDATE `field_config` SET `storage_type`= 'field_sql_norevisions', `storage_module`= 'field_sql_norevisions'

After that, you can delete all field_revision tables except field_collection_item_revision and node_revision.

I retrieved all my tables with this kind of query and used a regular expression to create DROP TABLE sql queries.

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE 'field_revision%'
AND TABLE_SCHEMA = 'my_db'

SELECT DISTINCT TABLE_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME LIKE 'field_revision%'

AND TABLE_SCHEMA = 'my_db'

For the two revision tables remaining, I just execute 2 SQL queries after each cron job to remove them :

DELETE FROM field_collection_item_revision
WHERE NOT EXISTS (
  SELECT NULL
  FROM field_collection_item
  WHERE field_collection_item.`revision_id` = field_collection_item_revision.revision_id
);

DELETE FROM node_revision
WHERE NOT EXISTS (
  SELECT NULL
  FROM node
  WHERE node.vid = node_revision.vid
);

DELETE FROM field_collection_item_revision

WHERE NOT EXISTS (

SELECT NULL

FROM field_collection_item

WHERE field_collection_item.`revision_id` = field_collection_item_revision.revision_id

);

DELETE FROM node_revision

WHERE NOT EXISTS (

SELECT NULL

FROM node

WHERE node.vid = node_revision.vid

);

Here we are, no more revisions ! Database has been half-sized cut and writing performances have been increased too !

10 Thoughts on “Make your Drupal 7 faster by removing all revisions”

Orfeo on December 2, 2013 at 00:57 said:

Hi, there is also a module https://drupal.org/project/node_revision_delete
But that module has the issue that some file upload links are broken after deleting revisions. Dit you check if that also happens with your script?
I don’t want to mess up a production website I want to use it on..
Thanks

Reply↓
- Admin on December 2, 2013 at 09:35 said:
  
  I don’t have this problem no. Thanks for sharing.
  
  Reply↓
Bernhard on February 3, 2014 at 14:29 said:

Looks promising. Alas Drupal Core itself has hard coded checks for ‘field_sql_storage’. See https://drupal.org/node/2167343. So this module will break some of the Drupal functionality.

Reply↓
- Admin on February 3, 2014 at 14:31 said:
  
  Yup. It’s hack and I don’t use the default term’s listing page. Hope I won’t have any new problem in the future.
  
  Reply↓
NoP on August 26, 2015 at 14:03 said:

Hi. Thanks for this post, it’s been quite useful. I copied it into the drupal site root folder although I had to add this at the beginning of the file:

define(‘DRUPAL_ROOT’, getcwd());
$_SERVER[‘REMOTE_ADDR’] = “127.0.0.1”;

After that, it worked perfectly.

Thanks!

Reply↓
Irfan on September 28, 2015 at 02:06 said:

Hi, where to put the first script that has drupal bootstrap? Is it in a custom module?

Reply↓
- admin on September 28, 2015 at 08:11 said:
  
  It’s in the root folder of drupal in a php file
  
  Reply↓
Crescenzo on March 5, 2017 at 23:14 said:

Hi, after this query “UPDATE field_config SET storage_type= ‘field_sql_norevisions’,
storage_module= ‘field_sql_norevisions'” all my content are empty. The node exist but is empty…why?

Reply↓
- admin on April 21, 2017 at 13:49 said:
  
  Because you need to recreate all nodes :)
  
  Reply↓
Toprak Erdem on October 24, 2018 at 09:04 said:

Hi there,
Thanks for great tutorial.I’m newb in drupal backend.. My database became around 2GB and half of them is revision. Logging session performance is exist for a few weeks when a user editing his profile or nodes.I hope deleting field revisions maybe helful for solution.Nevermind, this is another problem.
But I wonder the real point.The question is what field revisions for ? What’s benefit of field revision on drupal architecture.I have found some information but all about deleting issues. Could you give me a few words about what field revision for . I understand why we need to delete but wondering why we shouldn’t delete revisions.Thanks.

Reply↓