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 (
    86300, 86326, 86270, 86291, 86299, 86325, 
    86269, 86290, 86292, 86318, 86346, 
    86350, 86256, 86309, 86298, 86324, 
    86311, 86327, 86260, 86308, 86268, 
    86289, 86304, 86317, 86347, 86351, 
    86344, 86348, 86303, 86316, 86254, 
    86306, 86312, 86328, 86345, 86349, 
    86273, 86305, 86296, 86322, 86340, 
    86341, 86266, 86287, 86294, 86320, 
    86264, 86285, 95152, 95153, 95154, 
    95155, 95156, 95157, 95158, 95166, 
    95167, 95169, 95170, 95172, 95174, 
    95175, 95176, 95178, 95180, 95182, 
    95183, 95184, 95185, 95186, 95188, 
    95190, 95191, 95192, 95193, 95194, 
    95196, 95198, 95199, 95200, 95202, 
    95204, 95206, 95207, 95208, 95209, 
    95210, 95211, 95212, 95214, 95216, 
    95217, 95218, 95220, 95221, 95223
  )

Query time 0.00098

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 (86300,86326,86270,86291,86299,86325,86269,86290,86292,86318,86346,86350,86256,86309,86298,86324,86311,86327,86260,86308,86268,86289,86304,86317,86347,86351,86344,86348,86303,86316,86254,86306,86312,86328,86345,86349,86273,86305,86296,86322,86340,86341,86266,86287,86294,86320,86264,86285,95152,95153,95154,95155,95156,95157,95158,95166,95167,95169,95170,95172,95174,95175,95176,95178,95180,95182,95183,95184,95185,95186,95188,95190,95191,95192,95193,95194,95196,95198,95199,95200,95202,95204,95206,95207,95208,95209,95210,95211,95212,95214,95216,95217,95218,95220,95221,95223))",
          "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"
          ]
        }
      }
    ]
  }
}