SELECT 
  pfv.feature_id, 
  pfv.product_id, 
  pfv.variant_id, 
  gp.group_id 
FROM 
  cscart_product_features_values AS pfv 
  INNER JOIN cscart_product_variation_group_products AS gp ON pfv.product_id = gp.product_id 
  INNER JOIN cscart_product_variation_group_features AS gpf ON gpf.group_id = gp.group_id 
  AND gpf.feature_id = pfv.feature_id 
WHERE 
  pfv.lang_code = 'en' 
  AND gp.group_id IN (
    527, 528, 529, 530, 531, 532, 533, 534
  )

Query time 0.00149

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "341.16"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gp",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "rows_examined_per_scan": 133,
          "rows_produced_per_join": 133,
          "filtered": "100.00",
          "index_condition": "(`gaseus`.`gp`.`group_id` in (527,528,529,530,531,532,533,534))",
          "cost_info": {
            "read_cost": "48.56",
            "eval_cost": "13.30",
            "prefix_cost": "61.86",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "gpf",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "gaseus.gp.group_id"
          ],
          "rows_examined_per_scan": 3,
          "rows_produced_per_join": 399,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "99.75",
            "eval_cost": "39.90",
            "prefix_cost": "201.51",
            "data_read_per_join": "40K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "pfv",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "lang_code",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "fpl",
          "used_key_parts": [
            "feature_id",
            "product_id",
            "lang_code"
          ],
          "key_length": "12",
          "ref": [
            "gaseus.gpf.feature_id",
            "gaseus.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 399,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "99.75",
            "eval_cost": "39.90",
            "prefix_cost": "341.16",
            "data_read_per_join": "308K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 12116 1324 527
553 12117 2197 527
553 12118 1324 528
622 12118 2170 528
553 12119 1324 528
622 12119 2158 528
553 12120 1324 528
622 12120 2159 528
553 12121 1324 528
622 12121 2160 528
553 12122 1324 528
622 12122 2155 528
553 12123 1324 528
622 12123 2156 528
553 12124 1324 528
622 12124 2157 528
553 12125 2197 528
622 12125 2170 528
553 12126 2197 528
622 12126 2158 528
553 12127 2197 528
622 12127 2159 528
553 12128 2197 528
622 12128 2160 528
553 12129 2197 528
622 12129 2155 528
553 12130 2197 528
622 12130 2156 528
553 12131 2197 528
622 12131 2157 528
553 12132 1324 529
622 12132 2170 529
553 12133 1324 529
622 12133 2158 529
553 12134 1324 529
622 12134 2159 529
553 12135 1324 529
622 12135 2160 529
553 12136 1324 529
622 12136 2155 529
553 12137 1324 529
622 12137 2156 529
553 12138 1324 529
622 12138 2157 529
553 12139 2197 529
622 12139 2170 529
553 12140 2197 529
622 12140 2158 529
553 12141 2197 529
622 12141 2159 529
553 12142 2197 529
622 12142 2160 529
553 12143 2197 529
622 12143 2155 529
553 12144 2197 529
622 12144 2156 529
553 12145 2197 529
622 12145 2157 529
553 12146 1324 530
622 12146 2170 530
553 12147 1324 530
622 12147 2158 530
553 12148 1324 530
622 12148 2159 530
553 12149 1324 530
622 12149 2160 530
553 12150 1324 530
622 12150 2155 530
553 12151 1324 530
622 12151 2156 530
553 12152 1324 530
622 12152 2157 530
553 12153 2197 530
622 12153 2170 530
553 12154 2197 530
622 12154 2158 530
553 12155 2197 530
622 12155 2159 530
553 12156 2197 530
622 12156 2160 530
553 12157 2197 530
622 12157 2155 530
553 12158 2197 530
622 12158 2156 530
553 12159 2197 530
622 12159 2157 530
553 12160 1324 531
622 12160 2189 531
553 12161 1324 531
622 12161 2158 531
553 12162 1324 531
622 12162 2159 531
553 12163 1324 531
622 12163 2160 531
553 12164 1324 531
622 12164 2155 531
553 12165 1324 531
622 12165 2156 531
553 12166 1324 531
622 12166 2157 531
553 12167 2197 531
622 12167 2189 531
553 12168 2197 531
622 12168 2158 531
553 12169 2197 531
622 12169 2159 531
553 12170 2197 531
622 12170 2160 531
553 12171 2197 531
622 12171 2155 531
553 12172 2197 531
622 12172 2156 531
553 12173 2197 531
622 12173 2157 531
553 12174 1324 532
622 12174 2189 532
553 12175 1324 532
622 12175 2158 532
553 12176 1324 532
622 12176 2159 532
553 12177 1324 532
622 12177 2160 532
553 12178 1324 532
622 12178 2155 532
553 12179 1324 532
622 12179 2156 532
553 12180 1324 532
622 12180 2157 532
553 12181 2197 532
622 12181 2189 532
553 12182 2197 532
622 12182 2158 532
553 12183 2197 532
622 12183 2159 532
553 12184 2197 532
622 12184 2160 532
553 12185 2197 532
622 12185 2155 532
553 12186 2197 532
622 12186 2156 532
553 12187 2197 532
622 12187 2157 532
553 12188 1324 533
622 12188 2189 533
553 12189 1324 533
622 12189 2158 533
553 12190 1324 533
622 12190 2159 533
553 12191 1324 533
622 12191 2160 533
553 12192 1324 533
622 12192 2155 533
553 12193 1324 533
622 12193 2156 533
553 12194 1324 533
622 12194 2157 533
553 12195 2197 533
622 12195 2189 533
553 12196 2197 533
622 12196 2158 533
553 12197 2197 533
622 12197 2159 533
553 12198 2197 533
622 12198 2160 533
553 12199 2197 533
622 12199 2155 533
553 12200 2197 533
622 12200 2156 533
553 12201 2197 533
622 12201 2157 533
622 12202 2189 534
622 12203 2158 534
622 12204 2159 534
622 12205 2160 534
622 12206 2155 534
622 12207 2156 534