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 (
    1096, 1142, 1162, 1105, 1151, 1170, 1064, 
    1110, 1132, 1153, 1138, 1154, 1166, 
    1183, 1117, 1134, 1171, 1185, 1125, 
    1128, 1169, 1087, 1147, 1075, 1113, 
    1124, 1145, 1080, 1141, 1143, 1116, 
    1094, 1167, 1118, 1058, 1063, 1051
  )

Query time 0.00406

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "108.16"
    },
    "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": 86,
          "rows_produced_per_join": 86,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`gp`.`group_id` in (1096,1142,1162,1105,1151,1170,1064,1110,1132,1153,1138,1154,1166,1183,1117,1134,1171,1185,1125,1128,1169,1087,1147,1075,1113,1124,1145,1080,1141,1143,1116,1094,1167,1118,1058,1063,1051))",
          "cost_info": {
            "read_cost": "39.36",
            "eval_cost": "8.60",
            "prefix_cost": "47.96",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "gpf",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "softwarepirmam_hewadelivard_cscart_4.gp.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 86,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "21.50",
            "eval_cost": "8.60",
            "prefix_cost": "78.06",
            "data_read_per_join": "8K"
          },
          "used_columns": [
            "feature_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": "fpl",
          "used_key_parts": [
            "feature_id",
            "product_id",
            "lang_code"
          ],
          "key_length": "12",
          "ref": [
            "softwarepirmam_hewadelivard_cscart_4.gpf.feature_id",
            "softwarepirmam_hewadelivard_cscart_4.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 86,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "21.50",
            "eval_cost": "8.60",
            "prefix_cost": "108.16",
            "data_read_per_join": "66K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
656 10991 1600 1051
779 10991 9507 1051
656 10992 1596 1051
779 10992 9505 1051
779 11025 9529 1058
656 11025 1783 1058
779 11024 9528 1058
656 11024 9359 1058
779 11081 9517 1063
656 11081 1596 1063
779 11080 9517 1063
656 11080 1600 1063
779 11082 9506 1063
656 11082 9045 1063
656 11086 1674 1064
779 11086 9556 1064
656 11085 2077 1064
779 11085 9517 1064
656 11137 3037 1075
779 11137 9517 1075
656 11136 1596 1075
779 11136 9517 1075
656 11152 1674 1080
779 11152 9590 1080
656 11153 1596 1080
779 11153 9517 1080
656 11236 1600 1087
779 11236 9523 1087
656 11237 1596 1087
779 11237 9524 1087
771 11300 9323 1094
656 11300 1596 1094
779 11300 9524 1094
771 11301 9323 1094
656 11301 1674 1094
779 11301 9692 1094
771 11306 9358 1096
656 11306 6360 1096
779 11306 9694 1096
771 11307 9358 1096
656 11307 2077 1096
779 11307 9695 1096
656 11337 1596 1105
779 11337 9517 1105
656 11338 1674 1105
779 11338 9520 1105
656 11348 1600 1110
779 11348 9517 1110
656 11349 1674 1110
779 11349 9520 1110
656 11352 9751 1113
779 11352 9522 1113
656 11355 9753 1113
779 11355 9588 1113
656 11360 9772 1116
779 11360 9523 1116
656 11361 6360 1116
779 11361 9522 1116
656 11362 9045 1117
779 11362 9522 1117
656 11363 1596 1117
779 11363 9522 1117
656 11364 1600 1118
779 11364 9523 1118
656 11365 1674 1118
779 11365 9778 1118
771 11388 9866 1124
656 11388 3037 1124
779 11388 9796 1124
771 11389 9866 1124
656 11389 1596 1124
779 11389 9503 1124
771 11390 9408 1125
656 11390 1596 1125
779 11390 9517 1125
771 11391 9408 1125
656 11391 3037 1125
779 11391 9523 1125
656 11399 6360 1128
779 11399 9517 1128
656 11400 3037 1128
779 11400 9870 1128
771 11411 9346 1132
656 11411 1596 1132
779 11411 9517 1132
771 11412 9346 1132
656 11412 1596 1132
779 11412 9588 1132
771 11413 9346 1132
656 11413 2077 1132
779 11413 9588 1132
779 11416 9517 1134
656 11416 3037 1134
779 11415 9692 1134
656 11415 1600 1134
779 11414 9522 1134
656 11414 1596 1134
779 11417 9523 1134
656 11417 9516 1134
656 11430 1914 1138
779 11430 9523 1138
656 11431 1783 1138
779 11431 9588 1138
656 11439 1596 1141
779 11439 9517 1141
656 11441 3037 1141
779 11441 9588 1141
656 11445 1600 1141
779 11445 9588 1141
656 11446 1596 1142
779 11446 9517 1142
656 11447 3037 1142
779 11447 9588 1142
656 11448 1596 1142
779 11448 9627 1142
656 11453 1596 1143
779 11453 9517 1143
656 11454 3037 1143
779 11454 9588 1143
656 11455 9802 1143
779 11455 9627 1143
771 11458 9321 1145
656 11458 9045 1145
779 11458 9522 1145
771 11460 9321 1145
656 11460 1682 1145
779 11460 9692 1145
771 11462 9426 1147
656 11462 1596 1147
779 11462 9522 1147
771 11463 9426 1147
656 11463 3037 1147
779 11463 9517 1147
771 11472 9397 1151
656 11472 1596 1151
779 11472 9522 1151
771 11474 9397 1151
656 11474 1600 1151
779 11474 9692 1151
771 11475 9397 1151
656 11475 3037 1151
779 11475 9517 1151
771 11477 9866 1153
656 11477 1596 1153
779 11477 9522 1153
771 11478 9866 1153
656 11478 2077 1153
779 11478 9517 1153
771 11480 9866 1153
656 11480 9791 1153
779 11480 9522 1153
656 11479 6360 1154
779 11479 9522 1154
656 11481 1783 1154
779 11481 9523 1154
771 11508 9341 1162
656 11508 1596 1162
779 11508 9588 1162
771 11509 9341 1162
656 11509 3037 1162
779 11509 9588 1162
656 11520 1596 1166
779 11520 9522 1166
656 11521 3037 1166
779 11521 9517 1166
771 11522 10057 1167
656 11522 3037 1167
779 11522 9517 1167
771 11523 10057 1167
656 11523 3037 1167
779 11523 10058 1167
656 11530 1596 1169
779 11530 9517 1169
656 11532 1674 1169
779 11532 9556 1169
771 11531 9481 1170
656 11531 9486 1170
779 11531 9517 1170
771 11533 9481 1170
656 11533 9486 1170
779 11533 10062 1170
771 11534 9481 1170
656 11534 9489 1170
779 11534 9520 1170
656 11535 1596 1171
779 11535 9517 1171
656 11536 6869 1171
779 11536 9556 1171
771 11598 9398 1183
656 11598 8285 1183
779 11598 9522 1183
771 11599 9398 1183
656 11599 3037 1183
779 11599 9523 1183
771 11600 9398 1183
656 11600 6360 1183
779 11600 9522 1183
771 11633 9349 1185
656 11633 6360 1185
779 11633 9517 1185
771 11634 9349 1185
656 11634 1674 1185
779 11634 9556 1185
771 11635 9349 1185
656 11635 9637 1185
779 11635 9557 1185
771 11636 9349 1185
656 11636 1558 1185
779 11636 9557 1185