SELECT 
  pfv.variant_id, 
  pfv.position, 
  pfvd.variant 
FROM 
  cscart_product_feature_variants AS pfv 
  INNER JOIN cscart_product_feature_variant_descriptions AS pfvd ON pfv.variant_id = pfvd.variant_id 
  AND pfvd.lang_code = 'en' 
WHERE 
  pfv.variant_id IN (1193, 1195, 1197, 1196)

Query time 0.00040

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pfvd range PRIMARY PRIMARY 9 4 Using index condition
1 SIMPLE pfv eq_ref PRIMARY PRIMARY 3 cscart_dev_4_16.pfvd.variant_id 1

Result

variant_id position variant
1193 1 Small
1195 3 Medium
1196 6 XX Large
1197 5 X Large