SqlContentEntityStorage::loadFromDedicatedTables()() runs a query that does not use indexes if an older revision is being loaded

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.