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 (
    100164, 100165, 100166, 100167, 100168, 
    100374, 100375, 100380, 100381, 100382, 
    100383, 100392, 100399, 100433, 100436, 
    100437, 100479, 100480, 100481, 100484, 
    100485, 100486, 100501, 100502, 100598, 
    100601, 100677, 100678, 100679, 100680, 
    100681, 100682, 100683, 100684, 100685, 
    100686, 100687, 100688, 100689, 100690, 
    100691, 100692, 100693, 100694, 100695, 
    100700, 100701, 100702, 100703, 100704, 
    100705, 100706, 100707, 100708, 100709, 
    100710, 100711, 100712, 100713, 100714, 
    100715, 100716, 100717, 100718, 100719, 
    100720, 100721, 100722, 100723, 100724, 
    100725, 100726, 100727, 100728, 100729, 
    100730, 100731, 100732, 100733, 100734, 
    100735, 100736, 100737, 100738, 100739, 
    100740, 100741, 100742, 100743, 100744, 
    100745, 100746, 100747, 100748, 100749, 
    100750
  )

Query time 0.00076

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.80"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "f",
          "access_type": "ALL",
          "possible_keys": [
            "idx_group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "0.35",
            "data_read_per_join": "104"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "g",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "3",
          "ref": [
            "nuie_scalesta_net.f.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.10",
            "prefix_cost": "1.45",
            "data_read_per_join": "400"
          },
          "used_columns": [
            "id",
            "code"
          ]
        }
      },
      {
        "table": {
          "table_name": "p",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "nuie_scalesta_net.f.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "index_condition": "(`nuie_scalesta_net`.`p`.`product_id` in (100164,100165,100166,100167,100168,100374,100375,100380,100381,100382,100383,100392,100399,100433,100436,100437,100479,100480,100481,100484,100485,100486,100501,100502,100598,100601,100677,100678,100679,100680,100681,100682,100683,100684,100685,100686,100687,100688,100689,100690,100691,100692,100693,100694,100695,100700,100701,100702,100703,100704,100705,100706,100707,100708,100709,100710,100711,100712,100713,100714,100715,100716,100717,100718,100719,100720,100721,100722,100723,100724,100725,100726,100727,100728,100729,100730,100731,100732,100733,100734,100735,100736,100737,100738,100739,100740,100741,100742,100743,100744,100745,100746,100747,100748,100749,100750))",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "1.80",
            "data_read_per_join": "16"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ]
        }
      }
    ]
  }
}