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 = 1476 
  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.00295

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` = 1476) 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"
          ]
        }
      }
    ]
  }
}