How does MySQL coerce types during joins?
You need to make absolutely sure the character sets of the tables are identical. Keep in mind that a unicode issues could be at play here.

You could redo the query like this:

select FOOBAAR_hhs.relationship_group from FOOBAAR left join FOOBAAR_hhs
on FOOBAAR.relationship_group = FOOBAAR_hhs.relationship_group where new_hh_id = 15387929;


or

select FOOBAAR_hhs.relationship_group from FOOBAAR left join FOOBAAR_hhs
USING (relationship_group) where new_hh_id = 15387929;


Try the explain for these and see if it changes