SELECT 
  pfv.feature_id, 
  pfv.product_id, 
  pfv.variant_id, 
  gp.group_id 
FROM 
  cscart_product_features_values AS pfv 
  INNER JOIN cscart_product_variation_group_products AS gp ON pfv.product_id = gp.product_id 
  INNER JOIN cscart_product_variation_group_features AS gpf ON gpf.group_id = gp.group_id 
  AND gpf.feature_id = pfv.feature_id 
WHERE 
  pfv.lang_code = 'en' 
  AND gp.group_id IN (13, 11)

Query time 0.00053

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE gpf index PRIMARY,idx_group_id PRIMARY 6 6 Using where; Using index
1 SIMPLE gp ref PRIMARY,idx_group_id idx_group_id 3 cscart_dev_4_16.gpf.group_id 9
1 SIMPLE pfv ref PRIMARY,fl,lang_code,product_id,fpl,idx_product_feature_variant_id idx_product_feature_variant_id 12 cscart_dev_4_16.gp.product_id,cscart_dev_4_16.gpf.feature_id,const 1 Using where; Using index

Result

feature_id product_id variant_id group_id
548 1 1194 13
548 337 1193 13
548 338 1195 13
548 339 1197 13
548 340 1196 13
548 341 1194 13
548 342 1193 13
548 343 1195 13
548 344 1197 13
548 345 1196 13
548 346 1194 13
548 347 1193 13
548 348 1195 13
548 349 1197 13
548 350 1196 13
548 351 1194 13
548 352 1193 13
548 353 1195 13
548 354 1197 13
548 355 1196 13
549 149 1198 11
549 286 1199 11
549 287 1200 11
549 288 1201 11
549 1 1198 13
549 337 1198 13
549 338 1198 13
549 339 1198 13
549 340 1198 13
549 341 1199 13
549 342 1199 13
549 343 1199 13
549 344 1199 13
549 345 1199 13
549 346 1200 13
549 347 1200 13
549 348 1200 13
549 349 1200 13
549 350 1200 13
549 351 1201 13
549 352 1201 13
549 353 1201 13
549 354 1201 13
549 355 1201 13