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 (
    2187, 
    2244, 
    2172, 
    2171, 
    1668, 
    8586, 
    8372, 
    2222, 
    2175, 
    2228, 
    2220, 
    2177, 
    2230, 
    2226, 
    1667, 
    2221, 
    2179, 
    1666, 
    8240, 
    2183, 
    8813, 
    2176, 
    8433, 
    2224, 
    2195, 
    2225, 
    8514, 
    2189, 
    2196, 
    2186, 
    2180, 
    2178, 
    2191, 
    2170, 
    2181, 
    2190, 
    2185, 
    2198, 
    2182, 
    2188, 
    2173, 
    8784, 
    2193, 
    2184, 
    2197, 
    2174, 
    2199, 
    2219, 
    2223, 
    2194, 
    2243, 
    2200, 
    2229, 
    8789, 
    8695, 
    8766, 
    8802, 
    8362, 
    8573, 
    8696, 
    8243, 
    8617, 
    8601, 
    8375, 
    8570, 
    8812, 
    8536, 
    8604, 
    8589, 
    8699, 
    8590, 
    8612, 
    8800, 
    8702, 
    8576, 
    8701, 
    8464, 
    8469, 
    8577, 
    8708, 
    8703, 
    8783, 
    8804, 
    8275, 
    8569, 
    8724, 
    8445, 
    8470, 
    8435, 
    8801, 
    8805, 
    8811, 
    8815, 
    8441, 
    8446, 
    8543, 
    8245, 
    8440, 
    8519, 
    8593, 
    8595, 
    8432, 
    8444, 
    8471, 
    8594, 
    8472, 
    8457, 
    8806, 
    8828, 
    8830, 
    8575, 
    8269, 
    8466, 
    8363, 
    8459, 
    8460, 
    8603, 
    8581, 
    8709, 
    8438, 
    8799, 
    8831, 
    8241, 
    8272, 
    8273, 
    8276, 
    8365, 
    8382, 
    8538, 
    8572, 
    8578, 
    8580, 
    8585, 
    8597, 
    8614, 
    8615, 
    8729, 
    8730, 
    8757, 
    8797, 
    8810, 
    8814, 
    8829, 
    8242, 
    8244, 
    8277, 
    8280, 
    8373, 
    8377, 
    8403, 
    8431, 
    8434, 
    8443, 
    8517, 
    8540, 
    8574, 
    8587, 
    8596, 
    8600, 
    8605
  )

Query time 0.00139

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "225.16"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "p",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "rows_examined_per_scan": 161,
          "rows_produced_per_join": 161,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`p`.`product_id` in (2187,2244,2172,2171,1668,8586,8372,2222,2175,2228,2220,2177,2230,2226,1667,2221,2179,1666,8240,2183,8813,2176,8433,2224,2195,2225,8514,2189,2196,2186,2180,2178,2191,2170,2181,2190,2185,2198,2182,2188,2173,8784,2193,2184,2197,2174,2199,2219,2223,2194,2243,2200,2229,8789,8695,8766,8802,8362,8573,8696,8243,8617,8601,8375,8570,8812,8536,8604,8589,8699,8590,8612,8800,8702,8576,8701,8464,8469,8577,8708,8703,8783,8804,8275,8569,8724,8445,8470,8435,8801,8805,8811,8815,8441,8446,8543,8245,8440,8519,8593,8595,8432,8444,8471,8594,8472,8457,8806,8828,8830,8575,8269,8466,8363,8459,8460,8603,8581,8709,8438,8799,8831,8241,8272,8273,8276,8365,8382,8538,8572,8578,8580,8585,8597,8614,8615,8729,8730,8757,8797,8810,8814,8829,8242,8244,8277,8280,8373,8377,8403,8431,8434,8443,8517,8540,8574,8587,8596,8600,8605))",
          "cost_info": {
            "read_cost": "96.36",
            "eval_cost": "16.10",
            "prefix_cost": "112.46",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "g",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "3",
          "ref": [
            "softwarepirmam_hewadelivard_cscart_4.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 161,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "40.25",
            "eval_cost": "16.10",
            "prefix_cost": "168.81",
            "data_read_per_join": "62K"
          },
          "used_columns": [
            "id",
            "code"
          ]
        }
      },
      {
        "table": {
          "table_name": "f",
          "access_type": "ref",
          "possible_keys": [
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "softwarepirmam_hewadelivard_cscart_4.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 161,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "40.25",
            "eval_cost": "16.10",
            "prefix_cost": "225.16",
            "data_read_per_join": "16K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}