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 (517, 515, 513, 509, 507, 512)

Query time 0.00247

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "32.11"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gpf",
          "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": 12,
          "rows_produced_per_join": 12,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`gpf`.`group_id` in (517,515,513,509,507,512))",
          "cost_info": {
            "read_cost": "5.71",
            "eval_cost": "1.20",
            "prefix_cost": "6.91",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "gp",
          "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.gpf.group_id"
          ],
          "rows_examined_per_scan": 3,
          "rows_produced_per_join": 36,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "9.00",
            "eval_cost": "3.60",
            "prefix_cost": "19.51",
            "data_read_per_join": "576"
          },
          "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": "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": 36,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "9.00",
            "eval_cost": "3.60",
            "prefix_cost": "32.11",
            "data_read_per_join": "27K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
658 4301 5472 507
658 4303 5472 507
658 4304 5473 507
658 4305 5473 507
658 4306 5474 507
658 4307 5474 507
658 4308 5475 507
658 4309 5475 507
656 4301 1560 507
656 4303 1607 507
656 4304 1560 507
656 4305 1607 507
656 4306 1560 507
656 4307 1607 507
656 4308 1560 507
656 4309 1607 507
656 4319 1607 509
656 4320 1560 509
656 4321 1607 509
656 4311 1560 509
656 4315 1607 509
656 4316 1560 509
656 4317 1607 509
656 4318 1560 509
658 4319 5474 509
658 4320 5475 509
658 4321 5475 509
658 4311 5472 509
658 4315 5472 509
658 4316 5473 509
658 4317 5473 509
658 4318 5474 509
648 4369 1561 512
648 4363 1561 512
648 4366 1562 512
648 4367 1563 512
648 4368 1564 512
648 4370 1562 512
648 4379 1563 512
648 4380 1564 512
648 4378 1562 512
648 4377 1561 512
648 4376 1564 512
648 4375 1563 512
648 4374 1562 512
648 4373 1561 512
648 4372 1564 512
648 4371 1563 512
656 4369 1608 512
656 4363 1610 512
656 4366 1610 512
656 4367 1610 512
656 4368 1610 512
656 4370 1608 512
656 4379 1682 512
656 4380 1682 512
656 4378 1682 512
656 4377 1682 512
656 4376 1681 512
656 4375 1681 512
656 4374 1681 512
656 4373 1681 512
656 4372 1608 512
656 4371 1608 512
648 4406 1564 513
648 4401 1563 513
648 4400 1562 513
648 4399 1561 513
648 4398 1564 513
648 4397 1563 513
648 4396 1562 513
648 4395 1561 513
648 4394 1564 513
648 4405 1563 513
648 4404 1562 513
648 4403 1561 513
648 4402 1564 513
648 4393 1563 513
648 4392 1562 513
648 4384 1561 513
656 4406 1682 513
656 4401 1681 513
656 4400 1681 513
656 4399 1681 513
656 4398 1608 513
656 4397 1608 513
656 4396 1608 513
656 4395 1608 513
656 4394 1610 513
656 4405 1682 513
656 4404 1682 513
656 4403 1682 513
656 4402 1681 513
656 4393 1610 513
656 4392 1610 513
656 4384 1610 513
648 4425 1563 515
648 4419 1563 515
648 4420 1561 515
648 4421 1562 515
648 4422 1563 515
648 4423 1561 515
648 4424 1562 515
648 4426 1561 515
648 4416 1561 515
648 4418 1562 515
648 4427 1562 515
648 4428 1563 515
656 4425 1608 515
656 4419 1600 515
656 4420 1610 515
656 4421 1610 515
656 4422 1610 515
656 4423 1608 515
656 4424 1608 515
656 4426 1681 515
656 4416 1600 515
656 4418 1600 515
656 4427 1681 515
656 4428 1681 515
648 4432 1561 517
648 4438 1562 517
648 4439 1563 517
648 4440 1561 517
648 4441 1562 517
648 4442 1563 517
648 4443 1561 517
648 4444 1562 517
648 4448 1563 517
648 4445 1563 517
648 4446 1561 517
648 4447 1562 517
656 4432 1595 517
656 4438 1595 517
656 4439 1595 517
656 4440 1600 517
656 4441 1600 517
656 4442 1600 517
656 4443 1607 517
656 4444 1607 517
656 4448 1558 517
656 4445 1607 517
656 4446 1558 517
656 4447 1558 517