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 (
    1401, 1402, 1403, 1404, 1405, 1406, 1407, 
    1408, 1409, 1410, 1411, 1412, 1413, 
    1414, 1415, 1416, 5107, 5108, 5109, 
    5110, 5111, 5112, 5113, 5114, 5115, 
    5116, 5117, 5118, 5119, 5120, 5121, 
    5122, 5123, 5124, 5125, 5126, 5127, 
    5128, 5129, 5130, 340, 341, 342, 343, 
    344, 345, 346, 412, 413, 414, 1366, 1367, 
    1368, 1369, 1370, 1371, 1372, 1373, 
    1374, 1375, 1376, 1377, 1378, 1379, 
    1380, 1381, 1382, 1383, 1384, 1385, 
    1386, 1387, 1388, 1389, 1390, 1391, 
    1392, 3612, 3613, 3614, 3615, 4224, 
    4225, 4226, 4227, 4228, 4229, 4230, 
    5079, 5080, 5081, 5082, 5083, 5084, 
    5085, 5086
  )

Query time 0.00132

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "201.61"
    },
    "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": 96,
          "rows_produced_per_join": 96,
          "filtered": "100.00",
          "index_condition": "(`gaseus`.`p`.`product_id` in (1401,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,1416,5107,5108,5109,5110,5111,5112,5113,5114,5115,5116,5117,5118,5119,5120,5121,5122,5123,5124,5125,5126,5127,5128,5129,5130,340,341,342,343,344,345,346,412,413,414,1366,1367,1368,1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1381,1382,1383,1384,1385,1386,1387,1388,1389,1390,1391,1392,3612,3613,3614,3615,4224,4225,4226,4227,4228,4229,4230,5079,5080,5081,5082,5083,5084,5085,5086))",
          "cost_info": {
            "read_cost": "57.61",
            "eval_cost": "9.60",
            "prefix_cost": "67.21",
            "data_read_per_join": "1K"
          },
          "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": [
            "gaseus.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 96,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "24.00",
            "eval_cost": "9.60",
            "prefix_cost": "100.81",
            "data_read_per_join": "37K"
          },
          "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": [
            "gaseus.p.group_id"
          ],
          "rows_examined_per_scan": 3,
          "rows_produced_per_join": 288,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "72.00",
            "eval_cost": "28.80",
            "prefix_cost": "201.61",
            "data_read_per_join": "29K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}