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

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.

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.

 

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

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

 

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

  1. 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

  2. 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.

    • 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.

  3. 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!

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

  5. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

Post Navigation