How to use EntityFieldQuery etc. to return a particular field efficiently? A taxonomy has a lot of big fields attached, but one of these fields is a short text field called external_identifier.

In code, I need to obtain an array mapping external_identifiers to tids for a given list of external identifiers.

So in raw SQL this is very efficient:

SELECT entity_id, field_external_identifier_value
  FROM field_data_field_external_identifier
 WHERE field_external_identifier_value IN ('a123','b567',...);


I understand I should be using EntityFieldQuery, but this only gives me an array of tids, keyed by ...tid. Then if I loop those and call entity_load it will be doing lots of separate lookups and loading a whole load of stuff I don't need. This looks really inefficient.

I understand that EntityFieldQuery will only return Ids, so is there a better way?

Or do I just resort to SQL?
The safest way to get a field value in Drupal is to load the whole entity using the API - Drupal is a modular system and this allows other modules to interact with the data. There are ways (see Clive's answer) to load the field with the API without having to load the full entity - but this is still much less efficient than a raw SQL query.

The question however is how often you need to run this inefficient code. Presumably you only need to run this code when entities have been modified - so while caching entities is not an option, you could well cache your map and only rebuild it when an entity has been modified (ie. on cache clear).

If your entities are modified very often, then rather then rebuild your map every time, you could use hook_entity_update and then edit that entitie's entry in your map - so continuously modify your map.

This approach may or may not be faster than using an SQL query every time you need your map - it depends on how often entities change and how often you generate the map.

That being said, it is extremely unlikely that Drupal 7's core database schema is going to change at this stage - you'd be quite safe using direct SQL queries (in particular if this is your own code as opposed to a module you want to release).