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, 12, 10)

Query time 0.00177

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "131.22"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gpf",
          "access_type": "index",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id",
            "group_id"
          ],
          "key_length": "6",
          "rows_examined_per_scan": 22,
          "rows_produced_per_join": 10,
          "filtered": "45.45",
          "using_index": true,
          "cost_info": {
            "read_cost": "4.60",
            "eval_cost": "2.00",
            "prefix_cost": "6.60",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ],
          "attached_condition": "(`pankajecarter_systemfour`.`gpf`.`group_id` in (13,11,12,10))"
        }
      },
      {
        "table": {
          "table_name": "pfv",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "lang_code",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id"
          ],
          "key_length": "3",
          "ref": [
            "pankajecarter_systemfour.gpf.feature_id"
          ],
          "rows_examined_per_scan": 8,
          "rows_produced_per_join": 79,
          "filtered": "99.75",
          "using_index": true,
          "cost_info": {
            "read_cost": "12.86",
            "eval_cost": "15.96",
            "prefix_cost": "35.46",
            "data_read_per_join": "61K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ],
          "attached_condition": "(`pankajecarter_systemfour`.`pfv`.`lang_code` = 'en')"
        }
      },
      {
        "table": {
          "table_name": "gp",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id",
            "group_id"
          ],
          "key_length": "6",
          "ref": [
            "pankajecarter_systemfour.pfv.product_id",
            "pankajecarter_systemfour.gpf.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 79,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "79.80",
            "eval_cost": "15.96",
            "prefix_cost": "131.22",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
548 278 1195 10
548 279 1194 10
548 280 1195 10
548 281 1194 10
548 282 1195 10
548 283 1194 10
548 284 1193 10
548 287 1193 11
548 315 1195 11
548 329 1197 13
548 330 1195 13
548 331 1197 13
548 333 1197 13
548 334 1195 13
548 335 1197 13
548 337 1197 13
548 338 1195 13
548 339 1197 13
548 341 1197 13
548 342 1195 13
548 343 1197 13
548 371 1195 13
548 372 1197 13
548 373 1195 13
548 374 1197 13
548 375 1195 13
548 376 1197 13
548 377 1195 13
548 378 1197 13
549 278 1201 10
549 279 1201 10
549 280 1200 10
549 281 1200 10
549 282 1199 10
549 283 1199 10
549 284 1199 10
549 329 1199 13
549 330 1199 13
549 331 1199 13
549 333 1200 13
549 334 1200 13
549 335 1200 13
549 337 1199 13
549 338 1199 13
549 339 1199 13
549 341 1200 13
549 342 1200 13
549 343 1200 13
549 371 1199 13
549 372 1199 13
549 373 1200 13
549 374 1200 13
549 375 1199 13
549 376 1199 13
549 377 1200 13
549 378 1200 13
550 287 1202 11
550 315 1204 11
550 286 1202 12
550 329 1203 13
550 330 1203 13
550 331 1203 13
550 333 1203 13
550 334 1203 13
550 335 1203 13
550 337 1204 13
550 338 1204 13
550 339 1204 13
550 341 1204 13
550 342 1204 13
550 343 1204 13
550 371 1203 13
550 372 1203 13
550 373 1203 13
550 374 1203 13
550 375 1204 13
550 376 1204 13
550 377 1204 13
550 378 1204 13
551 287 1206 11
551 315 1208 11
551 286 1206 12
551 329 1207 13
551 330 1209 13
551 331 1209 13
551 333 1207 13
551 334 1209 13
551 335 1209 13
551 337 1207 13
551 338 1209 13
551 339 1209 13
551 341 1207 13
551 342 1209 13
551 343 1209 13
551 371 1208 13
551 372 1208 13
551 373 1208 13
551 374 1208 13
551 375 1208 13
551 376 1208 13
551 377 1208 13
551 378 1208 13