SELECT 
  cscart_settings_variants.*, 
  cscart_settings_descriptions.value, 
  cscart_settings_descriptions.object_type 
FROM 
  cscart_settings_variants 
  INNER JOIN cscart_settings_descriptions ON cscart_settings_descriptions.object_id = cscart_settings_variants.variant_id 
  AND object_type = 'V' 
WHERE 
  cscart_settings_variants.object_id = 6210 
  AND cscart_settings_descriptions.lang_code = 'en' 
ORDER BY 
  cscart_settings_variants.position

Query time 0.00057

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_settings_variants ALL PRIMARY 407 Using where; Using filesort
1 SIMPLE cscart_settings_descriptions eq_ref PRIMARY PRIMARY 14 cscart_dev_4_16.cscart_settings_variants.variant_id,const,const 1 Using index condition