How to delete all but the current revision from the field_collection_item_revision table?

Question

I have a Drupal 7.24 Website making heavy use of field collections.

The field_collection_item_revision keeps getting bigger and bigger (9'000'000 rows) and only 200'000 entries are in field_collection_item.

What is the most secure and efficient way to "clean" this table?

PS: A SQL request with nested selects is not an option, it's too slow.

Answer 1

(Assuming a MySQL database)

Execute the following MySQL request:

DELETE fcir FROM field_collection_item_revision AS fcir
  RIGHT JOIN field_collection_item AS fci 
    ON fcir.item_id = fci.item_id
  WHERE NOT fcir.revision_id = fci.revision_id;

We delete all revisions that are not present in the field_collection_item table under "revision_id".