SELECT 
  v.variant_id, 
  v.product_id, 
  vd.variant, 
  vf.position, 
  vf.color 
FROM 
  cscart_product_features_values v 
  LEFT JOIN cscart_product_feature_variant_descriptions vd ON v.variant_id = vd.variant_id 
  AND vd.lang_code = 'en' 
  LEFT JOIN cscart_product_feature_variants vf ON v.variant_id = vf.variant_id 
WHERE 
  1 
  AND v.feature_id = 1539 
  AND v.product_id IN (
    '70309', 
    '70319', 
    '70320', 
    '70321', 
    '70322', 
    '70323', 
    '70324', 
    '70325', 
    '70326', 
    '70327', 
    '70328', 
    '70329', 
    '70330', 
    '70331', 
    '70332', 
    '70333', 
    '70334', 
    '70335', 
    '70336', 
    '70337', 
    '70338', 
    '70339', 
    '70340', 
    '70341', 
    '70342', 
    '70343', 
    '70344', 
    '70345', 
    '70346', 
    '70347', 
    '70348', 
    '70349', 
    '70350', 
    '70351', 
    '70352', 
    '70353', 
    '70354', 
    '70355', 
    '70356', 
    '70357', 
    '70358', 
    '70359', 
    '70360', 
    '70361', 
    '70362', 
    '70363', 
    '70364', 
    '70365', 
    '70366', 
    '70367', 
    '70368', 
    '70369', 
    '70370', 
    '70371', 
    '70372', 
    '70373', 
    '70374', 
    '70375', 
    '70376', 
    '70377', 
    '70378', 
    '70379', 
    '70380', 
    '70381', 
    '70382', 
    '70383', 
    '70384', 
    '70385', 
    '70386', 
    '70387', 
    '70388', 
    '70389', 
    '70390', 
    '70391', 
    '70392', 
    '70393', 
    '70394', 
    '70395', 
    '70396', 
    '70397', 
    '70398', 
    '70399', 
    '70400', 
    '70401', 
    '70402', 
    '70403', 
    '70404', 
    '70405', 
    '70406', 
    '70407', 
    '70408', 
    '70409', 
    '70410', 
    '70411', 
    '70412', 
    '70413', 
    '70414', 
    '70415', 
    '70416', 
    '70417', 
    '70418', 
    '70419', 
    '70420', 
    '70421', 
    '70422', 
    '70423', 
    '70424', 
    '70425', 
    '70426', 
    '70427', 
    '70428', 
    '70429', 
    '70430', 
    '70431', 
    '70432', 
    '70433', 
    '70434', 
    '70435', 
    '70436', 
    '70437', 
    '70438', 
    '70439', 
    '70440', 
    '70441', 
    '70442', 
    '70443', 
    '70444', 
    '70445', 
    '70446', 
    '70447', 
    '70448', 
    '70449', 
    '70450', 
    '70451', 
    '70452', 
    '70453', 
    '70454', 
    '70455', 
    '70456', 
    '70457', 
    '70458', 
    '70459', 
    '70460', 
    '70461', 
    '70462', 
    '70463', 
    '70465', 
    '70467', 
    '70469', 
    '70471', 
    '70473', 
    '70475', 
    '70477', 
    '70479', 
    '70481', 
    '70483', 
    '70485', 
    '70487', 
    '70489', 
    '70491', 
    '70493', 
    '70495', 
    '70497', 
    '70499', 
    '70501', 
    '70503', 
    '70505', 
    '70507', 
    '70509', 
    '70511', 
    '70513', 
    '70515', 
    '70517', 
    '70519', 
    '70521', 
    '70523', 
    '70525', 
    '70527', 
    '70529', 
    '70531', 
    '70533', 
    '70535', 
    '70537', 
    '70539', 
    '70541', 
    '70543', 
    '70545', 
    '70547', 
    '70549', 
    '70551', 
    '70553', 
    '70555', 
    '70557', 
    '70559', 
    '70561', 
    '70563', 
    '70565', 
    '70567', 
    '70569', 
    '70571', 
    '70573', 
    '70575', 
    '70577', 
    '70579', 
    '70581', 
    '70583', 
    '70585', 
    '70587', 
    '70589', 
    '70591', 
    '70593', 
    '70595', 
    '70597', 
    '70599', 
    '70601', 
    '70603', 
    '70605', 
    '70607', 
    '70609', 
    '70611', 
    '70613', 
    '70615', 
    '70617', 
    '70619', 
    '70621', 
    '70623', 
    '70625', 
    '70627', 
    '70629', 
    '70631', 
    '70633', 
    '70635', 
    '70637', 
    '70639', 
    '70641', 
    '70642', 
    '70643', 
    '70644', 
    '70645', 
    '70646', 
    '70647', 
    '70648', 
    '70649', 
    '70650', 
    '70651', 
    '70652', 
    '70653', 
    '70654', 
    '70655', 
    '70656', 
    '70657', 
    '70658', 
    '70659', 
    '70660', 
    '70661', 
    '70662', 
    '70663', 
    '70664', 
    '70665', 
    '70666', 
    '70667', 
    '70668', 
    '70669', 
    '70670', 
    '70671', 
    '70672', 
    '70673', 
    '70674', 
    '70675', 
    '70676'
  )

Query time 0.00322

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "37.23"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "v",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id",
            "product_id"
          ],
          "key_length": "6",
          "rows_examined_per_scan": 270,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "3.26",
            "eval_cost": "0.54",
            "prefix_cost": "33.46",
            "data_read_per_join": "4K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ],
          "attached_condition": "((`cscartdb`.`v`.`feature_id` = 1539) and (`cscartdb`.`v`.`product_id` in ('70309','70319','70320','70321','70322','70323','70324','70325','70326','70327','70328','70329','70330','70331','70332','70333','70334','70335','70336','70337','70338','70339','70340','70341','70342','70343','70344','70345','70346','70347','70348','70349','70350','70351','70352','70353','70354','70355','70356','70357','70358','70359','70360','70361','70362','70363','70364','70365','70366','70367','70368','70369','70370','70371','70372','70373','70374','70375','70376','70377','70378','70379','70380','70381','70382','70383','70384','70385','70386','70387','70388','70389','70390','70391','70392','70393','70394','70395','70396','70397','70398','70399','70400','70401','70402','70403','70404','70405','70406','70407','70408','70409','70410','70411','70412','70413','70414','70415','70416','70417','70418','70419','70420','70421','70422','70423','70424','70425','70426','70427','70428','70429','70430','70431','70432','70433','70434','70435','70436','70437','70438','70439','70440','70441','70442','70443','70444','70445','70446','70447','70448','70449','70450','70451','70452','70453','70454','70455','70456','70457','70458','70459','70460','70461','70462','70463','70465','70467','70469','70471','70473','70475','70477','70479','70481','70483','70485','70487','70489','70491','70493','70495','70497','70499','70501','70503','70505','70507','70509','70511','70513','70515','70517','70519','70521','70523','70525','70527','70529','70531','70533','70535','70537','70539','70541','70543','70545','70547','70549','70551','70553','70555','70557','70559','70561','70563','70565','70567','70569','70571','70573','70575','70577','70579','70581','70583','70585','70587','70589','70591','70593','70595','70597','70599','70601','70603','70605','70607','70609','70611','70613','70615','70617','70619','70621','70623','70625','70627','70629','70631','70633','70635','70637','70639','70641','70642','70643','70644','70645','70646','70647','70648','70649','70650','70651','70652','70653','70654','70655','70656','70657','70658','70659','70660','70661','70662','70663','70664','70665','70666','70667','70668','70669','70670','70671','70672','70673','70674','70675','70676')))"
        }
      },
      {
        "table": {
          "table_name": "vd",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "variant_id",
            "lang_code"
          ],
          "key_length": "9",
          "ref": [
            "cscartdb.v.variant_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.35",
            "eval_cost": "0.54",
            "prefix_cost": "35.35",
            "data_read_per_join": "16K"
          },
          "used_columns": [
            "variant_id",
            "variant",
            "lang_code"
          ]
        }
      },
      {
        "table": {
          "table_name": "vf",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "variant_id"
          ],
          "key_length": "3",
          "ref": [
            "cscartdb.v.variant_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.35",
            "eval_cost": "0.54",
            "prefix_cost": "37.23",
            "data_read_per_join": "6K"
          },
          "used_columns": [
            "variant_id",
            "color",
            "position"
          ]
        }
      }
    ]
  }
}

Result

variant_id product_id variant position color
6268 70309 Small 0 #000000
6268 70319 Small 0 #000000
6268 70320 Small 0 #000000
6269 70321 Medium 1 #000000
6269 70322 Medium 1 #000000
6269 70323 Medium 1 #000000
6270 70324 Large 2 #000000
6270 70325 Large 2 #000000
6270 70326 Large 2 #000000
6268 70327 Small 0 #000000
6268 70328 Small 0 #000000
6268 70329 Small 0 #000000
6269 70330 Medium 1 #000000
6269 70331 Medium 1 #000000
6269 70332 Medium 1 #000000
6270 70333 Large 2 #000000
6270 70334 Large 2 #000000
6270 70335 Large 2 #000000
6268 70336 Small 0 #000000
6268 70337 Small 0 #000000
6268 70338 Small 0 #000000
6269 70339 Medium 1 #000000
6269 70340 Medium 1 #000000
6269 70341 Medium 1 #000000
6270 70342 Large 2 #000000
6270 70343 Large 2 #000000
6270 70344 Large 2 #000000
6268 70345 Small 0 #000000
6268 70346 Small 0 #000000
6268 70347 Small 0 #000000
6269 70348 Medium 1 #000000
6269 70349 Medium 1 #000000
6269 70350 Medium 1 #000000
6270 70351 Large 2 #000000
6270 70352 Large 2 #000000
6270 70353 Large 2 #000000
6268 70354 Small 0 #000000
6268 70355 Small 0 #000000
6268 70356 Small 0 #000000
6269 70357 Medium 1 #000000
6269 70358 Medium 1 #000000
6269 70359 Medium 1 #000000
6270 70360 Large 2 #000000
6270 70361 Large 2 #000000
6270 70362 Large 2 #000000
6268 70363 Small 0 #000000
6268 70364 Small 0 #000000
6268 70365 Small 0 #000000
6269 70366 Medium 1 #000000
6269 70367 Medium 1 #000000
6269 70368 Medium 1 #000000
6270 70369 Large 2 #000000
6270 70370 Large 2 #000000
6270 70371 Large 2 #000000
6268 70372 Small 0 #000000
6268 70373 Small 0 #000000
6268 70374 Small 0 #000000
6269 70375 Medium 1 #000000
6269 70376 Medium 1 #000000
6269 70377 Medium 1 #000000
6270 70378 Large 2 #000000
6270 70379 Large 2 #000000
6270 70380 Large 2 #000000
6268 70381 Small 0 #000000
6268 70382 Small 0 #000000
6268 70383 Small 0 #000000
6269 70384 Medium 1 #000000
6269 70385 Medium 1 #000000
6269 70386 Medium 1 #000000
6270 70387 Large 2 #000000
6270 70388 Large 2 #000000
6270 70389 Large 2 #000000
6268 70390 Small 0 #000000
6268 70391 Small 0 #000000
6268 70392 Small 0 #000000
6269 70393 Medium 1 #000000
6269 70394 Medium 1 #000000
6269 70395 Medium 1 #000000
6270 70396 Large 2 #000000
6270 70397 Large 2 #000000
6270 70398 Large 2 #000000
6268 70399 Small 0 #000000
6268 70400 Small 0 #000000
6268 70401 Small 0 #000000
6269 70402 Medium 1 #000000
6269 70403 Medium 1 #000000
6269 70404 Medium 1 #000000
6270 70405 Large 2 #000000
6270 70406 Large 2 #000000
6270 70407 Large 2 #000000
6268 70408 Small 0 #000000
6268 70409 Small 0 #000000
6268 70410 Small 0 #000000
6269 70411 Medium 1 #000000
6269 70412 Medium 1 #000000
6269 70413 Medium 1 #000000
6270 70414 Large 2 #000000
6270 70415 Large 2 #000000
6270 70416 Large 2 #000000
6268 70417 Small 0 #000000
6268 70418 Small 0 #000000
6268 70419 Small 0 #000000
6269 70420 Medium 1 #000000
6269 70421 Medium 1 #000000
6269 70422 Medium 1 #000000
6270 70423 Large 2 #000000
6270 70424 Large 2 #000000
6270 70425 Large 2 #000000
6268 70426 Small 0 #000000
6268 70427 Small 0 #000000
6268 70428 Small 0 #000000
6269 70429 Medium 1 #000000
6269 70430 Medium 1 #000000
6269 70431 Medium 1 #000000
6270 70432 Large 2 #000000
6270 70433 Large 2 #000000
6270 70434 Large 2 #000000
6268 70435 Small 0 #000000
6268 70436 Small 0 #000000
6268 70437 Small 0 #000000
6269 70438 Medium 1 #000000
6269 70439 Medium 1 #000000
6269 70440 Medium 1 #000000
6270 70441 Large 2 #000000
6270 70442 Large 2 #000000
6270 70443 Large 2 #000000
6268 70444 Small 0 #000000
6268 70445 Small 0 #000000
6268 70446 Small 0 #000000
6269 70447 Medium 1 #000000
6269 70448 Medium 1 #000000
6269 70449 Medium 1 #000000
6270 70450 Large 2 #000000
6270 70451 Large 2 #000000
6270 70452 Large 2 #000000
6268 70453 Small 0 #000000
6268 70454 Small 0 #000000
6268 70455 Small 0 #000000
6269 70456 Medium 1 #000000
6269 70457 Medium 1 #000000
6269 70458 Medium 1 #000000
6270 70459 Large 2 #000000
6270 70460 Large 2 #000000
6270 70461 Large 2 #000000
6268 70462 Small 0 #000000
6268 70463 Small 0 #000000
6268 70465 Small 0 #000000
6269 70467 Medium 1 #000000
6269 70469 Medium 1 #000000
6269 70471 Medium 1 #000000
6270 70473 Large 2 #000000
6270 70475 Large 2 #000000
6270 70477 Large 2 #000000
6268 70479 Small 0 #000000
6268 70481 Small 0 #000000
6268 70483 Small 0 #000000
6269 70485 Medium 1 #000000
6269 70487 Medium 1 #000000
6269 70489 Medium 1 #000000
6270 70491 Large 2 #000000
6270 70493 Large 2 #000000
6270 70495 Large 2 #000000
6268 70497 Small 0 #000000
6268 70499 Small 0 #000000
6268 70501 Small 0 #000000
6269 70503 Medium 1 #000000
6269 70505 Medium 1 #000000
6269 70507 Medium 1 #000000
6270 70509 Large 2 #000000
6270 70511 Large 2 #000000
6270 70513 Large 2 #000000
6268 70515 Small 0 #000000
6268 70517 Small 0 #000000
6268 70519 Small 0 #000000
6269 70521 Medium 1 #000000
6269 70523 Medium 1 #000000
6269 70525 Medium 1 #000000
6270 70527 Large 2 #000000
6270 70529 Large 2 #000000
6270 70531 Large 2 #000000
6268 70533 Small 0 #000000
6268 70535 Small 0 #000000
6268 70537 Small 0 #000000
6269 70539 Medium 1 #000000
6269 70541 Medium 1 #000000
6269 70543 Medium 1 #000000
6270 70545 Large 2 #000000
6270 70547 Large 2 #000000
6270 70549 Large 2 #000000
6268 70551 Small 0 #000000
6268 70553 Small 0 #000000
6268 70555 Small 0 #000000
6269 70557 Medium 1 #000000
6269 70559 Medium 1 #000000
6269 70561 Medium 1 #000000
6270 70563 Large 2 #000000
6270 70565 Large 2 #000000
6270 70567 Large 2 #000000
6268 70569 Small 0 #000000
6268 70571 Small 0 #000000
6268 70573 Small 0 #000000
6269 70575 Medium 1 #000000
6269 70577 Medium 1 #000000
6269 70579 Medium 1 #000000
6270 70581 Large 2 #000000
6270 70583 Large 2 #000000
6270 70585 Large 2 #000000
6268 70587 Small 0 #000000
6268 70589 Small 0 #000000
6268 70591 Small 0 #000000
6269 70593 Medium 1 #000000
6269 70595 Medium 1 #000000
6269 70597 Medium 1 #000000
6270 70599 Large 2 #000000
6270 70601 Large 2 #000000
6270 70603 Large 2 #000000
6268 70605 Small 0 #000000
6268 70607 Small 0 #000000
6268 70609 Small 0 #000000
6269 70611 Medium 1 #000000
6269 70613 Medium 1 #000000
6269 70615 Medium 1 #000000
6270 70617 Large 2 #000000
6270 70619 Large 2 #000000
6270 70621 Large 2 #000000
6268 70623 Small 0 #000000
6268 70625 Small 0 #000000
6268 70627 Small 0 #000000
6269 70629 Medium 1 #000000
6269 70631 Medium 1 #000000
6269 70633 Medium 1 #000000
6270 70635 Large 2 #000000
6270 70637 Large 2 #000000
6270 70639 Large 2 #000000
6268 70641 Small 0 #000000
6268 70642 Small 0 #000000
6268 70643 Small 0 #000000
6269 70644 Medium 1 #000000
6269 70645 Medium 1 #000000
6269 70646 Medium 1 #000000
6270 70647 Large 2 #000000
6270 70648 Large 2 #000000
6270 70649 Large 2 #000000
6268 70650 Small 0 #000000
6268 70651 Small 0 #000000
6268 70652 Small 0 #000000
6269 70653 Medium 1 #000000
6269 70654 Medium 1 #000000
6269 70655 Medium 1 #000000
6270 70656 Large 2 #000000
6270 70657 Large 2 #000000
6270 70658 Large 2 #000000
6268 70659 Small 0 #000000
6268 70660 Small 0 #000000
6268 70661 Small 0 #000000
6269 70662 Medium 1 #000000
6269 70663 Medium 1 #000000
6269 70664 Medium 1 #000000
6270 70665 Large 2 #000000
6270 70666 Large 2 #000000
6270 70667 Large 2 #000000
6268 70668 Small 0 #000000
6268 70669 Small 0 #000000
6268 70670 Small 0 #000000
6269 70671 Medium 1 #000000
6269 70672 Medium 1 #000000
6269 70673 Medium 1 #000000
6270 70674 Large 2 #000000
6270 70675 Large 2 #000000
6270 70676 Large 2 #000000