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 (21, 19, 17, 15, 14, 12)

Query time 0.00242

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "191.02"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gpf",
          "access_type": "index",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id",
            "group_id"
          ],
          "key_length": "6",
          "rows_examined_per_scan": 21,
          "rows_produced_per_join": 11,
          "filtered": "52.38",
          "using_index": true,
          "cost_info": {
            "read_cost": "4.19",
            "eval_cost": "2.20",
            "prefix_cost": "6.39",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ],
          "attached_condition": "(`atulecarter_atul_demo2`.`gpf`.`group_id` in (21,19,17,15,14,12))"
        }
      },
      {
        "table": {
          "table_name": "pfv",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "lang_code",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id"
          ],
          "key_length": "3",
          "ref": [
            "atulecarter_atul_demo2.gpf.feature_id"
          ],
          "rows_examined_per_scan": 11,
          "rows_produced_per_join": 120,
          "filtered": "99.82",
          "using_index": true,
          "cost_info": {
            "read_cost": "15.49",
            "eval_cost": "24.16",
            "prefix_cost": "46.08",
            "data_read_per_join": "93K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ],
          "attached_condition": "(`atulecarter_atul_demo2`.`pfv`.`lang_code` = 'en')"
        }
      },
      {
        "table": {
          "table_name": "gp",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id",
            "group_id"
          ],
          "key_length": "6",
          "ref": [
            "atulecarter_atul_demo2.pfv.product_id",
            "atulecarter_atul_demo2.gpf.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 120,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "120.78",
            "eval_cost": "24.16",
            "prefix_cost": "191.02",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
548 187 1194 17
548 318 1193 17
548 319 1197 17
548 320 1194 17
548 321 1195 17
548 322 1196 17
548 323 1193 17
548 324 1195 17
548 325 1196 17
548 326 1197 17
548 327 1195 17
548 328 1196 17
548 329 1194 17
548 330 1195 17
548 331 1196 17
548 332 1194 17
548 333 1195 17
548 334 1196 17
548 538 1195 17
548 540 1196 17
548 541 1193 17
548 544 1197 17
548 547 1194 17
548 549 1197 17
548 551 1193 17
548 552 1194 17
548 556 1193 17
548 559 1197 17
548 561 1193 17
548 564 1197 17
548 466 1193 19
548 467 1194 19
548 468 1195 19
548 469 1197 19
548 470 1196 19
548 471 1193 19
548 472 1194 19
548 473 1195 19
548 474 1197 19
548 475 1196 19
548 476 1193 19
548 477 1194 19
548 478 1195 19
548 479 1197 19
548 480 1196 19
548 481 1193 19
548 482 1194 19
548 483 1195 19
548 484 1197 19
548 485 1196 19
548 486 1193 19
548 487 1194 19
548 488 1195 19
548 489 1197 19
548 490 1196 19
548 491 1193 19
548 492 1194 19
548 493 1195 19
548 494 1197 19
548 495 1196 19
548 496 1193 19
548 497 1194 19
548 498 1195 19
548 499 1197 19
548 500 1196 19
548 501 1193 19
548 502 1194 19
548 503 1195 19
548 504 1197 19
548 505 1196 19
548 506 1193 19
548 507 1194 19
548 508 1195 19
548 509 1197 19
548 510 1196 19
548 511 1193 19
548 512 1194 19
548 513 1195 19
548 514 1197 19
548 515 1196 19
548 524 1193 21
548 525 1194 21
548 526 1193 21
548 527 1194 21
548 528 1193 21
548 531 1194 21
548 532 1193 21
548 533 1194 21
548 534 1193 21
548 535 1194 21
549 187 1199 17
549 318 1199 17
549 319 1199 17
549 320 1200 17
549 321 1200 17
549 322 1200 17
549 323 1201 17
549 324 1201 17
549 325 1201 17
549 326 1199 17
549 327 1199 17
549 328 1199 17
549 329 1200 17
549 330 1200 17
549 331 1200 17
549 332 1201 17
549 333 1201 17
549 334 1201 17
549 538 1199 17
549 540 1199 17
549 541 1200 17
549 544 1200 17
549 547 1201 17
549 549 1201 17
549 551 1199 17
549 552 1199 17
549 556 1200 17
549 559 1200 17
549 561 1201 17
549 564 1201 17
549 466 1200 19
549 467 1200 19
549 468 1200 19
549 469 1200 19
549 470 1200 19
549 471 1200 19
549 472 1200 19
549 473 1200 19
549 474 1200 19
549 475 1200 19
549 476 1200 19
549 477 1200 19
549 478 1200 19
549 479 1200 19
549 480 1200 19
549 481 1200 19
549 482 1200 19
549 483 1200 19
549 484 1200 19
549 485 1200 19
549 486 1200 19
549 487 1200 19
549 488 1200 19
549 489 1200 19
549 490 1200 19
549 491 1201 19
549 492 1201 19
549 493 1201 19
549 494 1201 19
549 495 1201 19
549 496 1201 19
549 497 1201 19
549 498 1201 19
549 499 1201 19
549 500 1201 19
549 501 1201 19
549 502 1201 19
549 503 1201 19
549 504 1201 19
549 505 1201 19
549 506 1201 19
549 507 1201 19
549 508 1201 19
549 509 1201 19
549 510 1201 19
549 511 1201 19
549 512 1201 19
549 513 1201 19
549 514 1201 19
549 515 1201 19
549 524 1198 21
549 525 1198 21
549 526 1198 21
549 527 1198 21
549 528 1199 21
549 531 1199 21
549 532 1201 21
549 533 1201 21
549 534 1201 21
549 535 1201 21
550 309 1203 12
550 187 1202 17
550 318 1202 17
550 319 1202 17
550 320 1202 17
550 321 1202 17
550 322 1202 17
550 323 1202 17
550 324 1202 17
550 325 1202 17
550 326 1203 17
550 327 1203 17
550 328 1203 17
550 329 1203 17
550 330 1203 17
550 331 1203 17
550 332 1203 17
550 333 1203 17
550 334 1203 17
550 538 1202 17
550 540 1202 17
550 541 1202 17
550 544 1202 17
550 547 1202 17
550 549 1202 17
550 551 1203 17
550 552 1203 17
550 556 1203 17
550 559 1203 17
550 561 1203 17
550 564 1203 17
551 312 1204 14
551 313 1205 14
551 314 1206 14
551 524 1204 21
551 525 1204 21
551 526 1205 21
551 527 1205 21
551 528 1204 21
551 531 1206 21
551 532 1204 21
551 533 1204 21
551 534 1205 21
551 535 1205 21
552 315 1207 15
552 316 1208 15
553 466 1213 19
553 467 1213 19
553 468 1213 19
553 469 1213 19
553 470 1213 19
553 471 1209 19
553 472 1209 19
553 473 1209 19
553 474 1209 19
553 475 1209 19
553 476 1210 19
553 477 1210 19
553 478 1210 19
553 479 1210 19
553 480 1210 19
553 481 1211 19
553 482 1211 19
553 483 1211 19
553 484 1211 19
553 485 1211 19
553 486 1212 19
553 487 1212 19
553 488 1212 19
553 489 1212 19
553 490 1212 19
553 491 1213 19
553 492 1213 19
553 493 1213 19
553 494 1213 19
553 495 1213 19
553 496 1209 19
553 497 1209 19
553 498 1209 19
553 499 1209 19
553 500 1209 19
553 501 1210 19
553 502 1210 19
553 503 1210 19
553 504 1210 19
553 505 1210 19
553 506 1211 19
553 507 1211 19
553 508 1211 19
553 509 1211 19
553 510 1211 19
553 511 1212 19
553 512 1212 19
553 513 1212 19
553 514 1212 19
553 515 1212 19