SELECT 
  cscart_state_descriptions.state 
FROM 
  cscart_states 
  LEFT JOIN cscart_state_descriptions ON cscart_state_descriptions.state_id = cscart_states.state_id 
  AND cscart_state_descriptions.lang_code = 'en' 
WHERE 
  cscart_states.country_code = 'UA' 
  AND cscart_states.code = '12'

Query time 0.00036

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_states const cs,code,country_code cs 106 const,const 1
1 SIMPLE cscart_state_descriptions const PRIMARY PRIMARY 9 const,const 1

Result

state
Dnipropetrovska oblast