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, 871, 872)

Query time 0.00454

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "280.56"
    },
    "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": 89,
          "rows_produced_per_join": 89,
          "filtered": "100.00",
          "index_condition": "(`test2_gaseus`.`gp`.`group_id` in (861,871,872))",
          "cost_info": {
            "read_cost": "31.91",
            "eval_cost": "8.90",
            "prefix_cost": "40.81",
            "data_read_per_join": "1K"
          },
          "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": 339,
          "filtered": "54.46",
          "using_index": true,
          "cost_info": {
            "read_cost": "58.69",
            "eval_cost": "33.93",
            "prefix_cost": "161.80",
            "data_read_per_join": "262K"
          },
          "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": 339,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "84.83",
            "eval_cost": "33.93",
            "prefix_cost": "280.56",
            "data_read_per_join": "34K"
          },
          "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 12653 1935 871
624 12653 1803 871
625 12653 2143 871
627 12653 1954 871
628 12653 1660 871
553 12652 1935 871
624 12652 1803 871
625 12652 2132 871
627 12652 1954 871
628 12652 1660 871
553 12651 1935 871
624 12651 1803 871
625 12651 2131 871
627 12651 1954 871
628 12651 1660 871
553 12650 1935 871
624 12650 1803 871
625 12650 2415 871
627 12650 1954 871
628 12650 1660 871
553 12649 1935 871
624 12649 1803 871
625 12649 2413 871
627 12649 1954 871
628 12649 1660 871
553 12648 1935 871
624 12648 1705 871
625 12648 2143 871
627 12648 1954 871
628 12648 1660 871
553 12647 1935 871
624 12647 1705 871
625 12647 2132 871
627 12647 1954 871
628 12647 1660 871
553 12646 1935 871
624 12646 1705 871
625 12646 2131 871
627 12646 1954 871
628 12646 1660 871
553 12645 1935 871
624 12645 1705 871
625 12645 2415 871
627 12645 1954 871
628 12645 1660 871
553 12644 1935 871
624 12644 1705 871
625 12644 2413 871
627 12644 1954 871
628 12644 1660 871
553 12643 1935 871
624 12643 1803 871
625 12643 2143 871
627 12643 1836 871
628 12643 1660 871
553 12642 1935 871
624 12642 1803 871
625 12642 2132 871
627 12642 1836 871
628 12642 1660 871
553 12641 1935 871
624 12641 1803 871
625 12641 2131 871
627 12641 1836 871
628 12641 1660 871
553 12640 1935 871
624 12640 1803 871
625 12640 2415 871
627 12640 1836 871
628 12640 1660 871
553 12639 1935 871
624 12639 1803 871
625 12639 2413 871
627 12639 1836 871
628 12639 1660 871
553 12624 1935 871
624 12624 1705 871
625 12624 2413 871
627 12624 2290 871
628 12624 1660 871
553 12625 1935 871
624 12625 1705 871
625 12625 2415 871
627 12625 2290 871
628 12625 1660 871
553 12626 1935 871
624 12626 1705 871
625 12626 2131 871
627 12626 2290 871
628 12626 1660 871
553 12627 1935 871
624 12627 1705 871
625 12627 2132 871
627 12627 2290 871
628 12627 1660 871
553 12628 1935 871
624 12628 1705 871
625 12628 2143 871
627 12628 2290 871
628 12628 1660 871
553 12629 1935 871
624 12629 1803 871
625 12629 2413 871
627 12629 2290 871
628 12629 1660 871
553 12630 1935 871
624 12630 1803 871
625 12630 2415 871
627 12630 2290 871
628 12630 1660 871
553 12631 1935 871
624 12631 1803 871
625 12631 2131 871
627 12631 2290 871
628 12631 1660 871
553 12632 1935 871
624 12632 1803 871
625 12632 2132 871
627 12632 2290 871
628 12632 1660 871
553 12633 1935 871
624 12633 1803 871
625 12633 2143 871
627 12633 2290 871
628 12633 1660 871
553 12634 1935 871
624 12634 1705 871
625 12634 2413 871
627 12634 1836 871
628 12634 1660 871
553 12635 1935 871
624 12635 1705 871
625 12635 2415 871
627 12635 1836 871
628 12635 1660 871
553 12636 1935 871
624 12636 1705 871
625 12636 2131 871
627 12636 1836 871
628 12636 1660 871
553 12637 1935 871
624 12637 1705 871
625 12637 2132 871
627 12637 1836 871
628 12637 1660 871
553 12638 1935 871
624 12638 1705 871
625 12638 2143 871
627 12638 1836 871
628 12638 1660 871
553 12683 1935 872
624 12683 1803 872
625 12683 2143 872
627 12683 1954 872
628 12683 1712 872
553 12661 1935 872
624 12661 1803 872
625 12661 2131 872
627 12661 2290 872
628 12661 1712 872
553 12662 1935 872
624 12662 1803 872
625 12662 2132 872
627 12662 2290 872
628 12662 1712 872
553 12663 1935 872
624 12663 1803 872
625 12663 2143 872
627 12663 2290 872
628 12663 1712 872
553 12664 1935 872
624 12664 1705 872
625 12664 2413 872
627 12664 1836 872
628 12664 1712 872
553 12665 1935 872
624 12665 1705 872
625 12665 2415 872
627 12665 1836 872
628 12665 1712 872
553 12666 1935 872
624 12666 1705 872
625 12666 2131 872
627 12666 1836 872
628 12666 1712 872
553 12667 1935 872
624 12667 1705 872
625 12667 2132 872
627 12667 1836 872
628 12667 1712 872
553 12668 1935 872
624 12668 1705 872
625 12668 2143 872
627 12668 1836 872
628 12668 1712 872
553 12669 1935 872
624 12669 1803 872
625 12669 2413 872
627 12669 1836 872
628 12669 1712 872
553 12670 1935 872
624 12670 1803 872
625 12670 2415 872
627 12670 1836 872
628 12670 1712 872
553 12671 1935 872
624 12671 1803 872
625 12671 2131 872
627 12671 1836 872
628 12671 1712 872
553 12672 1935 872
624 12672 1803 872
625 12672 2132 872
627 12672 1836 872
628 12672 1712 872
553 12673 1935 872
624 12673 1803 872
625 12673 2143 872
627 12673 1836 872
628 12673 1712 872
553 12674 1935 872
624 12674 1705 872
625 12674 2413 872
627 12674 1954 872
628 12674 1712 872
553 12675 1935 872
624 12675 1705 872
625 12675 2415 872
627 12675 1954 872
628 12675 1712 872
553 12676 1935 872
624 12676 1705 872
625 12676 2131 872
627 12676 1954 872
628 12676 1712 872
553 12677 1935 872
624 12677 1705 872
625 12677 2132 872
627 12677 1954 872
628 12677 1712 872
553 12678 1935 872
624 12678 1705 872
625 12678 2143 872
627 12678 1954 872
628 12678 1712 872
553 12679 1935 872
624 12679 1803 872
625 12679 2413 872
627 12679 1954 872
628 12679 1712 872
553 12680 1935 872
624 12680 1803 872
625 12680 2415 872
627 12680 1954 872
628 12680 1712 872
553 12681 1935 872
624 12681 1803 872
625 12681 2131 872
627 12681 1954 872
628 12681 1712 872
553 12682 1935 872
624 12682 1803 872
625 12682 2132 872
627 12682 1954 872
628 12682 1712 872
553 12660 1935 872
624 12660 1803 872
625 12660 2415 872
627 12660 2290 872
628 12660 1712 872
553 12659 1935 872
624 12659 1803 872
625 12659 2413 872
627 12659 2290 872
628 12659 1712 872
553 12658 1935 872
624 12658 1705 872
625 12658 2143 872
627 12658 2290 872
628 12658 1712 872
553 12657 1935 872
624 12657 1705 872
625 12657 2132 872
627 12657 2290 872
628 12657 1712 872
553 12656 1935 872
624 12656 1705 872
625 12656 2131 872
627 12656 2290 872
628 12656 1712 872
553 12655 1935 872
624 12655 1705 872
625 12655 2415 872
627 12655 2290 872
628 12655 1712 872
553 12654 1935 872
624 12654 1705 872
625 12654 2413 872
627 12654 2290 872
628 12654 1712 872