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 (
    86855, 84265, 89384, 89389, 89300, 89432, 
    89731, 89439, 82381, 89301, 82396, 
    86851, 89440, 82416, 86853, 89431, 
    89299, 84263, 84267, 89349, 89334, 
    83088, 89124, 89128, 89160, 89551, 
    82343, 89350, 89263, 89340, 82450, 
    82451, 86238, 91290, 84264, 91913, 
    91914, 91915, 91916, 91917, 91918, 
    91919, 82332, 86814, 86844, 89101, 
    89336, 89729, 89734, 91286, 82415, 
    86854, 89339, 82355, 90093, 90103, 
    90112, 89438, 82398, 86849, 89730, 
    91293, 89353, 82380, 89333, 89354, 
    82340, 82341, 82346, 86389, 86391, 
    89430, 83115, 83125, 83134, 82449, 
    86823, 89123, 89127, 89159, 89550, 
    89355, 89356, 83087, 89284, 89437, 
    91289, 84262, 84266, 90120, 89157, 
    89548, 90092, 90102, 90111, 89335
  )

Query time 0.00071

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 (86855,84265,89384,89389,89300,89432,89731,89439,82381,89301,82396,86851,89440,82416,86853,89431,89299,84263,84267,89349,89334,83088,89124,89128,89160,89551,82343,89350,89263,89340,82450,82451,86238,91290,84264,91913,91914,91915,91916,91917,91918,91919,82332,86814,86844,89101,89336,89729,89734,91286,82415,86854,89339,82355,90093,90103,90112,89438,82398,86849,89730,91293,89353,82380,89333,89354,82340,82341,82346,86389,86391,89430,83115,83125,83134,82449,86823,89123,89127,89159,89550,89355,89356,83087,89284,89437,91289,84262,84266,90120,89157,89548,90092,90102,90111,89335))",
          "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"
          ]
        }
      }
    ]
  }
}