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 (
    82485, 89533, 85606, 85605, 89532, 93902, 
    93903, 93904, 93905, 93906, 93910, 
    93911, 93912, 94965, 94966, 94967, 
    94968, 94969, 94970, 94971, 94972, 
    94973, 94974, 94975, 94976, 94977, 
    94978, 94979, 94980, 94981, 94982, 
    95450, 95459, 95463, 95511, 95512, 
    95513, 95517, 95518, 95519, 95527, 
    95528, 95542, 95543, 95544, 96792, 
    96848, 96849, 96850, 96851, 96852, 
    96853, 96854, 96855, 96856, 96857, 
    96858, 96859, 96860, 96861, 96862, 
    96863, 96864, 96865, 98509, 98510, 
    100187, 100188, 100189, 100190, 100191, 
    100195, 100196, 100197, 100298, 100300
  )

Query time 0.00095

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.05"
    },
    "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": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "0.70",
            "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 (82485,89533,85606,85605,89532,93902,93903,93904,93905,93906,93910,93911,93912,94965,94966,94967,94968,94969,94970,94971,94972,94973,94974,94975,94976,94977,94978,94979,94980,94981,94982,95450,95459,95463,95511,95512,95513,95517,95518,95519,95527,95528,95542,95543,95544,96792,96848,96849,96850,96851,96852,96853,96854,96855,96856,96857,96858,96859,96860,96861,96862,96863,96864,96865,98509,98510,100187,100188,100189,100190,100191,100195,100196,100197,100298,100300))",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "1.05",
            "data_read_per_join": "16"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ]
        }
      }
    ]
  }
}