metatag_entity_load is slow when node has thousands of revisions

Posted by mikeytown2 on January 28, 2014 at 3:41am

Just a heads up that the newer version of metatag has a bottleneck with lots of node revisions. https://drupal.org/node/2183203

In our case we had a node load take over 8 seconds because the node has over 100k revisions. Raw output from my debug code

hook_entity_load metatag in 8.2447 seconds
hook_entity_load rdf in 0 seconds
hook_TYPE_load webform in 0.0443 seconds
hook_TYPE_load bf_field_mapper in 0.0401 seconds
hook_TYPE_load comment in 0.0165 seconds
hook_TYPE_load print in 0.0159 seconds
hook_TYPE_load scheduler in 0.0159 seconds
hook_TYPE_load user in 0.0161 seconds
hook_TYPE_load print_mail in 0.016 seconds
hook_TYPE_load print_pdf in 0.016 seconds

Root issue is that metatag_metatags_load_multiple runs this query

  // Get all translations of tag data for this entity.
  $result = db_query("
    SELECT
      entity_id,
      revision_id,
      language,
      data
    FROM {metatag}
    WHERE (entity_type = :type)
    AND (entity_id IN (:ids))
    ORDER BY entity_id, revision_id", array(
      ':type' => $entity_type,
      ':ids' => $entity_ids,
  ));

This was not an issue with older versions of metatag.

Query used to find nodes with over 1k revisions (check to see if this bug might affect you).

SELECT
  node_count.counter, 
  node.*
FROM (
  SELECT 
    COUNT(nid) AS counter, 
    nid
  FROM node_revision
  GROUP BY nid
) AS node_count
INNER JOIN node AS node
  ON node_count.nid = node.nid
WHERE node_count.counter > 1000
ORDER BY node_count.counter DESC

You can see in the attached image how one of our tests went from 10 seconds to over 60 seconds because of this issue.