SELECT 
  f.feature_id, 
  f.purpose, 
  p.product_id, 
  p.parent_product_id, 
  g.id, 
  g.code 
FROM 
  cscart_product_variation_group_features AS f 
  INNER JOIN cscart_product_variation_groups AS g ON f.group_id = g.id 
  INNER JOIN cscart_product_variation_group_products AS p ON f.group_id = p.group_id 
WHERE 
  p.product_id IN (
    1542, 1540, 1538, 1532, 1528, 1517, 1428, 
    1406, 1319, 1284, 1274, 1273, 1266, 
    1263, 1256, 1255, 1143, 1134, 1126, 
    985, 944, 908, 855, 853
  )

Query time 0.00124

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.61"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "p",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "rows_examined_per_scan": 24,
          "rows_produced_per_join": 24,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`p`.`product_id` in (1542,1540,1538,1532,1528,1517,1428,1406,1319,1284,1274,1273,1266,1263,1256,1255,1143,1134,1126,985,944,908,855,853))",
          "cost_info": {
            "read_cost": "14.41",
            "eval_cost": "2.40",
            "prefix_cost": "16.81",
            "data_read_per_join": "384"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "g",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "3",
          "ref": [
            "softwarepirmam_hewadelivard_cscart_4.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 24,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "6.00",
            "eval_cost": "2.40",
            "prefix_cost": "25.21",
            "data_read_per_join": "9K"
          },
          "used_columns": [
            "id",
            "code"
          ]
        }
      },
      {
        "table": {
          "table_name": "f",
          "access_type": "ref",
          "possible_keys": [
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "softwarepirmam_hewadelivard_cscart_4.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 24,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "6.00",
            "eval_cost": "2.40",
            "prefix_cost": "33.61",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}

Result

feature_id purpose product_id parent_product_id id code
658 group_variation_catalog_item 853 0 65 PV-B58B7A325
656 group_variation_catalog_item 908 0 45 PV-8ACC44ABF
656 group_variation_catalog_item 944 0 50 PV-77CF72FFE
648 group_variation_catalog_item 944 0 50 PV-77CF72FFE
656 group_variation_catalog_item 985 0 54 PV-CF61221FE
648 group_variation_catalog_item 985 0 54 PV-CF61221FE
648 group_variation_catalog_item 1126 0 62 PV-30CD8C4BC
656 group_variation_catalog_item 1126 0 62 PV-30CD8C4BC
649 group_variation_catalog_item 1126 0 62 PV-30CD8C4BC
649 group_variation_catalog_item 1134 0 63 PV-A698F3E19
656 group_variation_catalog_item 1134 0 63 PV-A698F3E19
648 group_variation_catalog_item 1134 0 63 PV-A698F3E19
656 group_variation_catalog_item 1143 0 66 PV-C626C1CC1
643 group_variation_catalog_item 1256 0 85 PV-26D3640BD
656 group_variation_catalog_item 1256 0 85 PV-26D3640BD
648 group_variation_catalog_item 1256 0 85 PV-26D3640BD
643 group_variation_catalog_item 1263 0 94 PV-C3FFDD72E
656 group_variation_catalog_item 1263 0 94 PV-C3FFDD72E
648 group_variation_catalog_item 1263 0 94 PV-C3FFDD72E
643 group_variation_catalog_item 1266 0 92 PV-4169F669A
656 group_variation_catalog_item 1266 0 92 PV-4169F669A
648 group_variation_catalog_item 1266 0 92 PV-4169F669A
643 group_variation_catalog_item 1273 0 89 PV-52CC58E4E
656 group_variation_catalog_item 1273 0 89 PV-52CC58E4E
648 group_variation_catalog_item 1273 0 89 PV-52CC58E4E
643 group_variation_catalog_item 1274 0 102 PV-BC2CD4B96
656 group_variation_catalog_item 1274 0 102 PV-BC2CD4B96
648 group_variation_catalog_item 1274 0 102 PV-BC2CD4B96
649 group_variation_catalog_item 1284 0 98 PV-E42F45055
656 group_variation_catalog_item 1284 0 98 PV-E42F45055
643 group_variation_catalog_item 1319 0 76 PV-B1D601CEB
656 group_variation_catalog_item 1319 0 76 PV-B1D601CEB
648 group_variation_catalog_item 1319 0 76 PV-B1D601CEB
643 group_variation_catalog_item 1406 0 87 PV-74A04BB83
656 group_variation_catalog_item 1406 0 87 PV-74A04BB83
648 group_variation_catalog_item 1406 0 87 PV-74A04BB83
643 group_variation_catalog_item 1428 0 90 PV-959FDB46B
656 group_variation_catalog_item 1428 0 90 PV-959FDB46B
648 group_variation_catalog_item 1428 0 90 PV-959FDB46B
766 group_catalog_item 1517 0 818 PV-9D251377F
766 group_catalog_item 1528 0 820 PV-17ABB2FC2
766 group_catalog_item 1532 0 819 PV-538C8B469
766 group_catalog_item 1538 0 817 PV-A48CEF788
656 group_variation_catalog_item 1540 0 114 PV-4F9357752
656 group_variation_catalog_item 1542 0 115 PV-431188704