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 (
    98187, 98188, 98189, 98190, 98191, 98200, 
    98201, 98202, 98203, 98204, 98205, 
    98206, 98207, 98391, 98392, 98393, 
    98394, 98395, 98396, 98397, 98398, 
    98399, 98400, 98401, 98402, 98403, 
    98404, 98405, 98406, 98411, 98412, 
    98413, 98422, 98427, 98429, 98433, 
    98435, 98439, 98440, 98441, 98443, 
    98447, 98451, 98453, 98456, 98476, 
    98477, 98478, 98479, 98480, 98481, 
    98483, 98484, 98490, 98492, 98759, 
    98769, 98770, 98771, 98772, 98831, 
    98832, 98833, 98834, 98835, 98836, 
    98837, 98838, 98839, 98840, 98841, 
    98842, 98843, 98844, 98845, 98846, 
    98847, 98848, 98849, 98850, 98897, 
    98914, 98915, 98916, 98917, 98918, 
    98919, 98920, 98921, 98922, 98923, 
    98924, 98925, 98926, 98927, 98928
  )

Query time 0.00069

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 (98187,98188,98189,98190,98191,98200,98201,98202,98203,98204,98205,98206,98207,98391,98392,98393,98394,98395,98396,98397,98398,98399,98400,98401,98402,98403,98404,98405,98406,98411,98412,98413,98422,98427,98429,98433,98435,98439,98440,98441,98443,98447,98451,98453,98456,98476,98477,98478,98479,98480,98481,98483,98484,98490,98492,98759,98769,98770,98771,98772,98831,98832,98833,98834,98835,98836,98837,98838,98839,98840,98841,98842,98843,98844,98845,98846,98847,98848,98849,98850,98897,98914,98915,98916,98917,98918,98919,98920,98921,98922,98923,98924,98925,98926,98927,98928))",
          "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"
          ]
        }
      }
    ]
  }
}