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 (
    4065, 4142, 4143, 4144, 4145, 4146, 4147, 
    4148, 4195, 4196, 4197, 4198, 4199, 
    4200, 4201, 6716, 6715, 3117, 3119, 
    3127, 3133, 3141, 3142, 6674, 6680, 
    6692, 6693, 6701, 6707, 1417, 1393, 
    1394, 1395, 1396, 1397, 1398, 1399, 
    1400, 1401, 1402, 1403, 1404, 1405, 
    1406, 1407, 1408, 1409, 1410, 1411, 
    1412, 1413, 1414, 1415, 1416, 5107, 
    5108, 5109, 5110, 5111, 5112, 5113, 
    5114, 5115, 5116, 5117, 5118, 5119, 
    5120, 5121, 5122, 5123, 5124, 5125, 
    5126, 5127, 5128, 5129, 5130, 340, 341, 
    342, 343, 344, 345, 346, 412, 413, 414, 
    1366, 1367, 1368, 1369, 1370, 1371, 
    1372, 1373
  )

Query time 0.00102

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "336.01"
    },
    "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": 96,
          "rows_produced_per_join": 96,
          "filtered": "100.00",
          "index_condition": "(`test2_gaseus`.`p`.`product_id` in (4065,4142,4143,4144,4145,4146,4147,4148,4195,4196,4197,4198,4199,4200,4201,6716,6715,3117,3119,3127,3133,3141,3142,6674,6680,6692,6693,6701,6707,1417,1393,1394,1395,1396,1397,1398,1399,1400,1401,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,1416,5107,5108,5109,5110,5111,5112,5113,5114,5115,5116,5117,5118,5119,5120,5121,5122,5123,5124,5125,5126,5127,5128,5129,5130,340,341,342,343,344,345,346,412,413,414,1366,1367,1368,1369,1370,1371,1372,1373))",
          "cost_info": {
            "read_cost": "57.61",
            "eval_cost": "9.60",
            "prefix_cost": "67.21",
            "data_read_per_join": "1K"
          },
          "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": [
            "test2_gaseus.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 96,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "24.00",
            "eval_cost": "9.60",
            "prefix_cost": "100.81",
            "data_read_per_join": "37K"
          },
          "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": [
            "test2_gaseus.p.group_id"
          ],
          "rows_examined_per_scan": 7,
          "rows_produced_per_join": 672,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "168.00",
            "eval_cost": "67.20",
            "prefix_cost": "336.01",
            "data_read_per_join": "68K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}