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 = 1538 
  AND v.product_id IN (
    '69973', 
    '69977', 
    '69976', 
    '69978', 
    '69979', 
    '69980', 
    '69981', 
    '69982', 
    '69983', 
    '69984', 
    '69985', 
    '69986', 
    '69987', 
    '69988', 
    '69989', 
    '69990', 
    '69991', 
    '69992', 
    '69993', 
    '69994', 
    '69995', 
    '69996', 
    '69997', 
    '69998', 
    '69999', 
    '70000', 
    '70001', 
    '70002', 
    '70003', 
    '70004', 
    '70005', 
    '70006', 
    '70007', 
    '70008', 
    '70009', 
    '70010', 
    '70011', 
    '70012', 
    '70013', 
    '70014', 
    '70015', 
    '70016', 
    '70017', 
    '70018', 
    '70019', 
    '70020', 
    '70021', 
    '70022', 
    '70023', 
    '70024', 
    '70025', 
    '70026', 
    '70027', 
    '70028', 
    '70029', 
    '70030', 
    '70031', 
    '70032', 
    '70033', 
    '70034', 
    '70035', 
    '70036', 
    '70037', 
    '70038', 
    '70039', 
    '70040', 
    '70041', 
    '70042', 
    '70043', 
    '70044', 
    '70045', 
    '70046', 
    '70047', 
    '70048', 
    '70049', 
    '70050', 
    '70051', 
    '70052', 
    '70053', 
    '70054', 
    '70055', 
    '70056', 
    '70057', 
    '70058', 
    '70059', 
    '70060', 
    '70061', 
    '70062', 
    '70063', 
    '70064', 
    '70065', 
    '70066', 
    '70067', 
    '70068', 
    '70069', 
    '70070', 
    '70071', 
    '70072', 
    '70073', 
    '70074', 
    '70075', 
    '70076', 
    '70077', 
    '70078', 
    '70079', 
    '70080', 
    '70081', 
    '70082', 
    '70083', 
    '70084', 
    '70085', 
    '70086', 
    '70087', 
    '70088', 
    '70089', 
    '70090', 
    '70091', 
    '70092', 
    '70093', 
    '70094', 
    '70095', 
    '70096', 
    '70097', 
    '70098', 
    '70099', 
    '70100', 
    '70101', 
    '70102', 
    '70103', 
    '70104', 
    '70105', 
    '70106', 
    '70107', 
    '70108', 
    '70109', 
    '70110', 
    '70111', 
    '70112', 
    '70113', 
    '70114', 
    '70115', 
    '70116', 
    '70117', 
    '70118', 
    '70119', 
    '70120', 
    '70121', 
    '70122', 
    '70123', 
    '70124', 
    '70125', 
    '70126', 
    '70127', 
    '70128', 
    '70129', 
    '70130', 
    '70131', 
    '70132', 
    '70133', 
    '70134', 
    '70135', 
    '70136', 
    '70137', 
    '70138', 
    '70139', 
    '70140', 
    '70141', 
    '70142', 
    '70143', 
    '70144', 
    '70145', 
    '70146', 
    '70147', 
    '70148', 
    '70149', 
    '70150', 
    '70151', 
    '70152', 
    '70153', 
    '70154', 
    '70155', 
    '70156', 
    '70157', 
    '70158', 
    '70159', 
    '70160', 
    '70161', 
    '70162', 
    '70163', 
    '70164', 
    '70165', 
    '70166', 
    '70167', 
    '70168', 
    '70169', 
    '70170', 
    '70171', 
    '70172', 
    '70173', 
    '70174', 
    '70175', 
    '70176', 
    '70177', 
    '70178', 
    '70179', 
    '70180', 
    '70181', 
    '70182', 
    '70183', 
    '70184', 
    '70185', 
    '70186', 
    '70187', 
    '70188', 
    '70189', 
    '70190', 
    '70191', 
    '70192', 
    '70193', 
    '70194', 
    '70195', 
    '70196', 
    '70197', 
    '70198', 
    '70199', 
    '70200', 
    '70201', 
    '70202', 
    '70203', 
    '70204', 
    '70205', 
    '70206', 
    '70207', 
    '70208', 
    '70209', 
    '70210', 
    '70211', 
    '70212', 
    '70213', 
    '70214', 
    '70215', 
    '70216', 
    '70217', 
    '70218', 
    '70219', 
    '70220', 
    '70221', 
    '70222', 
    '70223', 
    '70224', 
    '70225', 
    '70226', 
    '70227', 
    '70228', 
    '70229', 
    '70230', 
    '70231', 
    '70232', 
    '70233', 
    '70234', 
    '70235', 
    '70236', 
    '70237', 
    '70238', 
    '70239', 
    '70240', 
    '70241', 
    '70242', 
    '70243', 
    '70244'
  )

Query time 0.00429

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` = 1538) and (`cscartdb`.`v`.`product_id` in ('69973','69977','69976','69978','69979','69980','69981','69982','69983','69984','69985','69986','69987','69988','69989','69990','69991','69992','69993','69994','69995','69996','69997','69998','69999','70000','70001','70002','70003','70004','70005','70006','70007','70008','70009','70010','70011','70012','70013','70014','70015','70016','70017','70018','70019','70020','70021','70022','70023','70024','70025','70026','70027','70028','70029','70030','70031','70032','70033','70034','70035','70036','70037','70038','70039','70040','70041','70042','70043','70044','70045','70046','70047','70048','70049','70050','70051','70052','70053','70054','70055','70056','70057','70058','70059','70060','70061','70062','70063','70064','70065','70066','70067','70068','70069','70070','70071','70072','70073','70074','70075','70076','70077','70078','70079','70080','70081','70082','70083','70084','70085','70086','70087','70088','70089','70090','70091','70092','70093','70094','70095','70096','70097','70098','70099','70100','70101','70102','70103','70104','70105','70106','70107','70108','70109','70110','70111','70112','70113','70114','70115','70116','70117','70118','70119','70120','70121','70122','70123','70124','70125','70126','70127','70128','70129','70130','70131','70132','70133','70134','70135','70136','70137','70138','70139','70140','70141','70142','70143','70144','70145','70146','70147','70148','70149','70150','70151','70152','70153','70154','70155','70156','70157','70158','70159','70160','70161','70162','70163','70164','70165','70166','70167','70168','70169','70170','70171','70172','70173','70174','70175','70176','70177','70178','70179','70180','70181','70182','70183','70184','70185','70186','70187','70188','70189','70190','70191','70192','70193','70194','70195','70196','70197','70198','70199','70200','70201','70202','70203','70204','70205','70206','70207','70208','70209','70210','70211','70212','70213','70214','70215','70216','70217','70218','70219','70220','70221','70222','70223','70224','70225','70226','70227','70228','70229','70230','70231','70232','70233','70234','70235','70236','70237','70238','70239','70240','70241','70242','70243','70244')))"
        }
      },
      {
        "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"
          ]
        }
      }
    ]
  }
}