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 (
    1, 
    228, 
    8, 
    9, 
    10, 
    8483, 
    71, 
    4, 
    2359, 
    1532, 
    8499, 
    8502, 
    8501, 
    2459, 
    1528, 
    2432, 
    2455, 
    1517, 
    2438, 
    8498, 
    7624, 
    8484, 
    8497, 
    2812, 
    7608, 
    8485, 
    1538, 
    8488, 
    8486, 
    8492, 
    8487, 
    7614, 
    8503, 
    8505, 
    7613, 
    8496, 
    8489, 
    7248, 
    7615, 
    2465, 
    7290, 
    8490, 
    7283, 
    8491, 
    8493, 
    7625, 
    7285, 
    7294, 
    7261, 
    7611, 
    7612, 
    2441, 
    7616, 
    7293, 
    7279, 
    7617, 
    7620, 
    7618, 
    7619, 
    7281, 
    7610, 
    7277, 
    7609, 
    7623, 
    7621, 
    7622, 
    2832, 
    8478, 
    8474, 
    8475, 
    6380, 
    10136, 
    10137, 
    8512, 
    3506, 
    8513, 
    8510, 
    4949, 
    10135, 
    5985, 
    8511, 
    2462, 
    2827, 
    5767, 
    6459, 
    8717, 
    5971, 
    2439, 
    10138, 
    6451, 
    8716, 
    8711, 
    2833, 
    8266, 
    8265, 
    8267, 
    8712, 
    6381, 
    5986, 
    8264, 
    8509, 
    8507, 
    8508, 
    6447, 
    6373, 
    6440, 
    8714, 
    8713, 
    8720, 
    8718, 
    8719, 
    8715, 
    6590, 
    6379, 
    8722, 
    6336, 
    8494, 
    8506, 
    6444, 
    8721, 
    5973, 
    5989, 
    5994, 
    6593, 
    6594, 
    9625, 
    6592, 
    10127, 
    10193, 
    6454, 
    10133, 
    10095, 
    10120, 
    10128, 
    10122, 
    10217, 
    10312, 
    10196, 
    10315, 
    10317, 
    10198, 
    10139, 
    10201, 
    10216, 
    10320, 
    10200, 
    10121, 
    10134, 
    10321, 
    10322, 
    10323, 
    10314, 
    10319, 
    9642, 
    10199, 
    10310, 
    10316, 
    8947, 
    10202, 
    10309
  )

Query time 0.00321

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "235.51"
    },
    "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": 170,
          "rows_produced_per_join": 170,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`p`.`product_id` in (1,228,8,9,10,8483,71,4,2359,1532,8499,8502,8501,2459,1528,2432,2455,1517,2438,8498,7624,8484,8497,2812,7608,8485,1538,8488,8486,8492,8487,7614,8503,8505,7613,8496,8489,7248,7615,2465,7290,8490,7283,8491,8493,7625,7285,7294,7261,7611,7612,2441,7616,7293,7279,7617,7620,7618,7619,7281,7610,7277,7609,7623,7621,7622,2832,8478,8474,8475,6380,10136,10137,8512,3506,8513,8510,4949,10135,5985,8511,2462,2827,5767,6459,8717,5971,2439,10138,6451,8716,8711,2833,8266,8265,8267,8712,6381,5986,8264,8509,8507,8508,6447,6373,6440,8714,8713,8720,8718,8719,8715,6590,6379,8722,6336,8494,8506,6444,8721,5973,5989,5994,6593,6594,9625,6592,10127,10193,6454,10133,10095,10120,10128,10122,10217,10312,10196,10315,10317,10198,10139,10201,10216,10320,10200,10121,10134,10321,10322,10323,10314,10319,9642,10199,10310,10316,8947,10202,10309))",
          "cost_info": {
            "read_cost": "99.51",
            "eval_cost": "17.00",
            "prefix_cost": "116.51",
            "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": 170,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "42.50",
            "eval_cost": "17.00",
            "prefix_cost": "176.01",
            "data_read_per_join": "66K"
          },
          "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": 170,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "42.50",
            "eval_cost": "17.00",
            "prefix_cost": "235.51",
            "data_read_per_join": "17K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}

Result

feature_id purpose product_id parent_product_id id code
766 group_catalog_item 1517 0 818 PV-9D251377F
766 group_catalog_item 1528 0 820 PV-17ABB2FC2
766 group_catalog_item 1532 0 819 PV-538C8B469
766 group_catalog_item 1538 0 817 PV-A48CEF788
766 group_catalog_item 2432 0 812 PV-02A3AD241
766 group_catalog_item 2438 0 814 PV-26DC85BFE
766 group_catalog_item 2441 0 807 PV-D8A5D371B
766 group_catalog_item 2455 0 813 PV-4DAAA7BAA
766 group_catalog_item 2459 0 815 PV-4FF6E4832
766 group_catalog_item 2465 0 809 PV-37DEF310A
766 group_catalog_item 4949 0 823 PV-C0CE6EB3D
766 group_catalog_item 5971 0 800 PV-E2E5B3DBA
766 group_catalog_item 5985 0 798 PV-E66B95606
766 group_catalog_item 5986 0 799 PV-EDE23DF2F
766 group_catalog_item 6336 0 821 PV-6797C3450
766 group_catalog_item 6373 0 822 PV-5AC970727
766 group_catalog_item 6440 0 841 PV-F1C196F48
766 group_catalog_item 6444 0 842 PV-EAA5D0142
766 group_catalog_item 6447 0 839 PV-D638FAA8D
766 group_catalog_item 6451 0 840 PV-3E950DD77
766 group_catalog_item 6459 0 838 PV-B6EEEABAA
766 group_catalog_item 6590 0 816 PV-8D2D460C8
766 group_catalog_item 7248 0 767 PV-53BABB20A
766 group_catalog_item 7261 0 766 PV-4DB6AA00D
766 group_catalog_item 7277 0 772 PV-C458A5A3F
766 group_catalog_item 7279 0 765 PV-46D60D76E
766 group_catalog_item 7281 0 768 PV-041A30818
766 group_catalog_item 7283 0 769 PV-BC4E00BE9
766 group_catalog_item 7285 0 770 PV-B0DDC8BC9
766 group_catalog_item 7290 0 771 PV-48B14BD34
766 group_catalog_item 7293 0 773 PV-1948CF268
766 group_catalog_item 7608 0 765 PV-46D60D76E
766 group_catalog_item 7609 0 766 PV-4DB6AA00D
766 group_catalog_item 7610 0 766 PV-4DB6AA00D
766 group_catalog_item 7611 0 766 PV-4DB6AA00D
766 group_catalog_item 7612 0 767 PV-53BABB20A
766 group_catalog_item 7613 0 767 PV-53BABB20A
766 group_catalog_item 7614 0 767 PV-53BABB20A
766 group_catalog_item 7615 0 768 PV-041A30818
766 group_catalog_item 7616 0 769 PV-BC4E00BE9
766 group_catalog_item 7617 0 770 PV-B0DDC8BC9
766 group_catalog_item 7618 0 770 PV-B0DDC8BC9
766 group_catalog_item 7619 0 770 PV-B0DDC8BC9
766 group_catalog_item 7620 0 770 PV-B0DDC8BC9
766 group_catalog_item 7621 0 771 PV-48B14BD34
766 group_catalog_item 7622 0 771 PV-48B14BD34
766 group_catalog_item 7623 0 772 PV-C458A5A3F
766 group_catalog_item 7624 0 772 PV-C458A5A3F
766 group_catalog_item 7625 0 773 PV-1948CF268
766 group_catalog_item 8264 0 798 PV-E66B95606
766 group_catalog_item 8265 0 799 PV-EDE23DF2F
766 group_catalog_item 8266 0 800 PV-E2E5B3DBA
766 group_catalog_item 8267 0 800 PV-E2E5B3DBA
766 group_catalog_item 8474 0 807 PV-D8A5D371B
766 group_catalog_item 8475 0 807 PV-D8A5D371B
766 group_catalog_item 8478 0 809 PV-37DEF310A
766 group_catalog_item 8483 0 812 PV-02A3AD241
766 group_catalog_item 8484 0 812 PV-02A3AD241
766 group_catalog_item 8485 0 812 PV-02A3AD241
766 group_catalog_item 8486 0 813 PV-4DAAA7BAA
766 group_catalog_item 8487 0 813 PV-4DAAA7BAA
766 group_catalog_item 8488 0 813 PV-4DAAA7BAA
766 group_catalog_item 8489 0 814 PV-26DC85BFE
766 group_catalog_item 8490 0 814 PV-26DC85BFE
766 group_catalog_item 8491 0 815 PV-4FF6E4832
766 group_catalog_item 8492 0 815 PV-4FF6E4832
766 group_catalog_item 8493 0 815 PV-4FF6E4832
766 group_catalog_item 8494 0 816 PV-8D2D460C8
766 group_catalog_item 8496 0 817 PV-A48CEF788
766 group_catalog_item 8497 0 818 PV-9D251377F
766 group_catalog_item 8498 0 818 PV-9D251377F
766 group_catalog_item 8499 0 819 PV-538C8B469
766 group_catalog_item 8501 0 819 PV-538C8B469
766 group_catalog_item 8502 0 819 PV-538C8B469
766 group_catalog_item 8503 0 820 PV-17ABB2FC2
766 group_catalog_item 8505 0 820 PV-17ABB2FC2
766 group_catalog_item 8506 0 821 PV-6797C3450
766 group_catalog_item 8507 0 822 PV-5AC970727
766 group_catalog_item 8508 0 822 PV-5AC970727
766 group_catalog_item 8509 0 822 PV-5AC970727
766 group_catalog_item 8510 0 823 PV-C0CE6EB3D
766 group_catalog_item 8511 0 823 PV-C0CE6EB3D
766 group_catalog_item 8512 0 823 PV-C0CE6EB3D
766 group_catalog_item 8513 0 823 PV-C0CE6EB3D
766 group_catalog_item 8711 0 838 PV-B6EEEABAA
766 group_catalog_item 8712 0 838 PV-B6EEEABAA
766 group_catalog_item 8713 0 839 PV-D638FAA8D
766 group_catalog_item 8714 0 839 PV-D638FAA8D
766 group_catalog_item 8715 0 839 PV-D638FAA8D
766 group_catalog_item 8716 0 840 PV-3E950DD77
766 group_catalog_item 8717 0 840 PV-3E950DD77
766 group_catalog_item 8718 0 841 PV-F1C196F48
766 group_catalog_item 8719 0 841 PV-F1C196F48
766 group_catalog_item 8720 0 841 PV-F1C196F48
766 group_catalog_item 8721 0 842 PV-EAA5D0142
766 group_catalog_item 8722 0 842 PV-EAA5D0142
766 group_catalog_item 8947 0 867 PV-07A60785C
766 group_catalog_item 10122 0 956 PV-3500758B9
766 group_catalog_item 10127 0 956 PV-3500758B9
766 group_catalog_item 10128 0 956 PV-3500758B9
766 group_catalog_item 10133 0 956 PV-3500758B9
766 group_catalog_item 10135 0 958 PV-F936216BA
766 group_catalog_item 10136 0 958 PV-F936216BA
766 group_catalog_item 10137 0 958 PV-F936216BA
766 group_catalog_item 10138 0 958 PV-F936216BA
766 group_catalog_item 10198 0 966 PV-522210B7E
766 group_catalog_item 10199 0 966 PV-522210B7E
766 group_catalog_item 10200 0 966 PV-522210B7E
766 group_catalog_item 10201 0 966 PV-522210B7E
766 group_catalog_item 10216 0 969 PV-E6DC5B696
766 group_catalog_item 10217 0 969 PV-E6DC5B696
766 group_catalog_item 10309 0 969 PV-E6DC5B696
766 group_catalog_item 10310 0 980 PV-EEE11970C
766 group_catalog_item 10312 0 981 PV-1D546C07D
766 group_catalog_item 10314 0 983 PV-D1F015AB2
766 group_catalog_item 10315 0 981 PV-1D546C07D
766 group_catalog_item 10316 0 982 PV-5DCEDC4C5
766 group_catalog_item 10317 0 981 PV-1D546C07D
766 group_catalog_item 10319 0 983 PV-D1F015AB2
766 group_catalog_item 10320 0 983 PV-D1F015AB2
766 group_catalog_item 10321 0 983 PV-D1F015AB2
766 group_catalog_item 10322 0 982 PV-5DCEDC4C5