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 (861, 894)

Query time 0.00274

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "138.84"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gp",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "rows_examined_per_scan": 44,
          "rows_produced_per_join": 44,
          "filtered": "100.00",
          "index_condition": "(`test2_gaseus`.`gp`.`group_id` in (861,894))",
          "cost_info": {
            "read_cost": "15.91",
            "eval_cost": "4.40",
            "prefix_cost": "20.31",
            "data_read_per_join": "704"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "pfv",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "lang_code",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "idx_product_feature_variant_id",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "ref": [
            "test2_gaseus.gp.product_id"
          ],
          "rows_examined_per_scan": 7,
          "rows_produced_per_join": 167,
          "filtered": "54.46",
          "using_index": true,
          "cost_info": {
            "read_cost": "29.01",
            "eval_cost": "16.78",
            "prefix_cost": "80.12",
            "data_read_per_join": "129K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ],
          "attached_condition": "(`test2_gaseus`.`pfv`.`lang_code` = 'en')"
        }
      },
      {
        "table": {
          "table_name": "gpf",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id",
            "group_id"
          ],
          "key_length": "6",
          "ref": [
            "test2_gaseus.pfv.feature_id",
            "test2_gaseus.gp.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 167,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "41.94",
            "eval_cost": "16.78",
            "prefix_cost": "138.84",
            "data_read_per_join": "17K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 12483 1935 861
624 12483 1803 861
625 12483 2421 861
627 12483 1836 861
628 12483 1660 861
553 12484 1935 861
624 12484 1705 861
625 12484 2413 861
627 12484 1954 861
628 12484 1660 861
553 12485 1935 861
624 12485 1705 861
625 12485 2415 861
627 12485 1954 861
628 12485 1660 861
553 12486 1935 861
624 12486 1705 861
625 12486 2419 861
627 12486 1954 861
628 12486 1660 861
553 12487 1935 861
624 12487 1705 861
625 12487 2420 861
627 12487 1954 861
628 12487 1660 861
553 12488 1935 861
624 12488 1705 861
625 12488 2421 861
627 12488 1954 861
628 12488 1660 861
553 12489 1935 861
624 12489 1803 861
625 12489 2413 861
627 12489 1954 861
628 12489 1660 861
553 12490 1935 861
624 12490 1803 861
625 12490 2415 861
627 12490 1954 861
628 12490 1660 861
553 12491 1935 861
624 12491 1803 861
625 12491 2419 861
627 12491 1954 861
628 12491 1660 861
553 12482 1935 861
624 12482 1803 861
625 12482 2420 861
627 12482 1836 861
628 12482 1660 861
553 12481 1935 861
624 12481 1803 861
625 12481 2419 861
627 12481 1836 861
628 12481 1660 861
553 12480 1935 861
624 12480 1803 861
625 12480 2415 861
627 12480 1836 861
628 12480 1660 861
553 12479 1935 861
624 12479 1803 861
625 12479 2413 861
627 12479 1836 861
628 12479 1660 861
553 12478 1935 861
624 12478 1705 861
625 12478 2421 861
627 12478 1836 861
628 12478 1660 861
553 12477 1935 861
624 12477 1705 861
625 12477 2420 861
627 12477 1836 861
628 12477 1660 861
553 12476 1935 861
624 12476 1705 861
625 12476 2419 861
627 12476 1836 861
628 12476 1660 861
553 12475 1935 861
624 12475 1705 861
625 12475 2415 861
627 12475 1836 861
628 12475 1660 861
553 12474 1935 861
624 12474 1705 861
625 12474 2413 861
627 12474 1836 861
628 12474 1660 861
553 12473 1935 861
624 12473 1803 861
625 12473 2421 861
627 12473 2290 861
628 12473 1660 861
553 12472 1935 861
624 12472 1803 861
625 12472 2420 861
627 12472 2290 861
628 12472 1660 861
553 12471 1935 861
624 12471 1803 861
625 12471 2419 861
627 12471 2290 861
628 12471 1660 861
553 12470 1935 861
624 12470 1803 861
625 12470 2415 861
627 12470 2290 861
628 12470 1660 861
553 12469 1935 861
624 12469 1803 861
625 12469 2413 861
627 12469 2290 861
628 12469 1660 861
553 12468 1935 861
624 12468 1705 861
625 12468 2421 861
627 12468 2290 861
628 12468 1660 861
553 12467 1935 861
624 12467 1705 861
625 12467 2420 861
627 12467 2290 861
628 12467 1660 861
553 12466 1935 861
624 12466 1705 861
625 12466 2419 861
627 12466 2290 861
628 12466 1660 861
553 12465 1935 861
624 12465 1705 861
625 12465 2415 861
627 12465 2290 861
628 12465 1660 861
553 12464 1935 861
624 12464 1705 861
625 12464 2413 861
627 12464 2290 861
628 12464 1660 861
553 12493 1935 861
624 12493 1803 861
625 12493 2421 861
627 12493 1954 861
628 12493 1660 861
553 12492 1935 861
624 12492 1803 861
625 12492 2420 861
627 12492 1954 861
628 12492 1660 861
553 12974 1973 894
595 12974 1904 894
622 12974 2497 894
623 12974 2508 894
624 12974 1803 894
625 12974 2124 894
626 12974 1723 894
627 12974 1659 894
628 12974 1660 894
636 12974 2348 894
637 12974 2345 894
646 12974 2369 894
647 12974 2373 894
553 12975 1973 894
595 12975 1904 894
622 12975 2497 894
623 12975 2508 894
624 12975 1803 894
625 12975 2124 894
626 12975 1723 894
627 12975 1659 894
628 12975 1660 894
636 12975 2348 894
637 12975 2345 894
646 12975 2370 894
647 12975 2374 894
553 12976 1973 894
595 12976 1905 894
622 12976 2497 894
623 12976 2508 894
624 12976 1803 894
625 12976 2124 894
626 12976 1723 894
627 12976 1659 894
628 12976 1660 894
636 12976 2348 894
637 12976 2345 894
646 12976 2369 894
647 12976 2373 894
553 12977 1973 894
595 12977 1905 894
622 12977 2497 894
623 12977 2508 894
624 12977 1803 894
625 12977 2124 894
626 12977 1723 894
627 12977 1659 894
628 12977 1660 894
636 12977 2348 894
637 12977 2345 894
646 12977 2370 894
647 12977 2374 894
553 12978 1973 894
595 12978 1789 894
622 12978 2497 894
623 12978 2508 894
624 12978 1803 894
625 12978 2124 894
626 12978 1723 894
627 12978 1659 894
628 12978 1660 894
636 12978 2348 894
637 12978 2345 894
646 12978 2369 894
647 12978 2373 894
553 12979 1973 894
595 12979 1789 894
622 12979 2497 894
623 12979 2508 894
624 12979 1803 894
625 12979 2124 894
626 12979 1723 894
627 12979 1659 894
628 12979 1660 894
636 12979 2348 894
637 12979 2345 894
646 12979 2370 894
647 12979 2374 894
553 12980 1973 894
595 12980 2499 894
622 12980 2497 894
623 12980 2508 894
624 12980 1803 894
625 12980 2124 894
626 12980 1723 894
627 12980 1659 894
628 12980 1660 894
636 12980 2348 894
637 12980 2345 894
646 12980 2369 894
647 12980 2373 894
553 12981 1973 894
595 12981 2499 894
622 12981 2497 894
623 12981 2508 894
624 12981 1803 894
625 12981 2124 894
626 12981 1723 894
627 12981 1659 894
628 12981 1660 894
636 12981 2348 894
637 12981 2345 894
646 12981 2370 894
647 12981 2374 894
553 12982 1973 894
595 12982 1787 894
622 12982 2497 894
623 12982 2508 894
624 12982 1803 894
625 12982 2124 894
626 12982 1723 894
627 12982 1659 894
628 12982 1660 894
636 12982 2348 894
637 12982 2345 894
646 12982 2369 894
647 12982 2373 894
553 12983 1973 894
595 12983 1787 894
622 12983 2497 894
623 12983 2508 894
624 12983 1803 894
625 12983 2124 894
626 12983 1723 894
627 12983 1659 894
628 12983 1660 894
636 12983 2348 894
637 12983 2345 894
646 12983 2370 894
647 12983 2374 894
553 12984 1973 894
595 12984 1900 894
622 12984 2497 894
623 12984 2508 894
624 12984 1803 894
625 12984 2124 894
626 12984 1723 894
627 12984 1659 894
628 12984 1660 894
636 12984 2348 894
637 12984 2345 894
646 12984 2369 894
647 12984 2373 894
553 12985 1973 894
595 12985 1900 894
622 12985 2497 894
623 12985 2508 894
624 12985 1803 894
625 12985 2124 894
626 12985 1723 894
627 12985 1659 894
628 12985 1660 894
636 12985 2348 894
637 12985 2345 894
646 12985 2370 894
647 12985 2374 894
553 12986 1973 894
595 12986 1899 894
622 12986 2497 894
623 12986 2508 894
624 12986 1803 894
625 12986 2124 894
626 12986 1723 894
627 12986 1659 894
628 12986 1660 894
636 12986 2348 894
637 12986 2345 894
646 12986 2369 894
647 12986 2373 894
553 12987 1973 894
595 12987 1899 894
622 12987 2497 894
623 12987 2508 894
624 12987 1803 894
625 12987 2124 894
626 12987 1723 894
627 12987 1659 894
628 12987 1660 894
636 12987 2348 894
637 12987 2345 894
646 12987 2370 894
647 12987 2374 894