Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.If we are loading a non-current revision, the query run looks something like:
SELECT t.*
FROM field_revision_body t
WHERE (entity_type = 'node')
AND (revision_id IN ('792'))
AND (language IN ('und'))
AND (deleted = '0')
ORDER BY delta ASC
Explain:
+----+-------------+-------+------+--------------------------------------------------+-------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------------------------------------+-------------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | t | ref | PRIMARY,entity_type,deleted,revision_id,language | revision_id | 4 | const | 1 | Using where; Using filesort |
+----+-------------+-------+------+--------------------------------------------------+-------------+---------+-------+------+-----------------------------+
Yuck!
If we simply add the entity ID as well, we see much better results.
SELECT t.*
FROM field_revision_body t
WHERE (entity_type = 'node')
AND (entity_id IN ('780'))
AND (revision_id IN ('792'))
AND (language IN ('und'))
AND (deleted = '0')
ORDER BY delta ASC
+----+-------------+-------+------+------------------------------------------------------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------------------------------------------+---------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | t | ref | PRIMARY,entity_type,deleted,entity_id,revision_id,language | PRIMARY | 395 | const,const,const,const | 1 | Using where |
+----+-------------+-------+------+------------------------------------------------------------+---------+---------+-------------------------+------+-------------+
This becomes important for any site using something like Workbench module where it is common for the publicly displayed version of a node to be an older revision.
Comments
Comment #9
quietone CreditAttribution: quietone as a volunteer commentedfield_sql_storage_field_storage_load() was removed in #1497374: Switch from Field-based storage to Entity-based storage in Sep 2013. I expect that improvements are happening in the entity system component.
Therefore, closing as outdated. If this is incorrect reopen the issue, by setting the status to 'Active', and add a comment explaining what still needs to be done.
Thanks!