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 = 1546 
  AND v.product_id IN (
    '71049', 
    '71050', 
    '71051', 
    '71052', 
    '71053', 
    '71054', 
    '71055', 
    '71056', 
    '71057', 
    '71058', 
    '71059', 
    '71060', 
    '71061', 
    '71062', 
    '71063', 
    '71064', 
    '71065', 
    '71066', 
    '71067', 
    '71068', 
    '71069', 
    '71070', 
    '71071', 
    '71072', 
    '71073', 
    '71074', 
    '71075', 
    '71076', 
    '71077', 
    '71078', 
    '71079', 
    '71080', 
    '71081', 
    '71082', 
    '71083', 
    '71084', 
    '71085', 
    '71086', 
    '71087', 
    '71088', 
    '71089', 
    '71090', 
    '71091', 
    '71092', 
    '71093', 
    '71094', 
    '71095', 
    '71096', 
    '71097', 
    '71098', 
    '71099', 
    '71100', 
    '71101', 
    '71102', 
    '71103', 
    '71104', 
    '71105', 
    '71106', 
    '71107', 
    '71108', 
    '71109', 
    '71110', 
    '71111', 
    '71112', 
    '71113', 
    '71114', 
    '71115', 
    '71116', 
    '71117', 
    '71118', 
    '71119', 
    '71120', 
    '71121', 
    '71122', 
    '71123', 
    '71124', 
    '71125', 
    '71126', 
    '71127', 
    '71128', 
    '71129', 
    '71130', 
    '71131', 
    '71132', 
    '71133', 
    '71134', 
    '71135', 
    '71136', 
    '71137', 
    '71138', 
    '71139', 
    '71140', 
    '71141', 
    '71142', 
    '71143', 
    '71144', 
    '71145', 
    '71146', 
    '71147', 
    '71148', 
    '71149', 
    '71150', 
    '71151', 
    '71152', 
    '71153', 
    '71154', 
    '71155', 
    '71156', 
    '71157', 
    '71158', 
    '71159', 
    '71160', 
    '71161', 
    '71162', 
    '71163', 
    '71164', 
    '71165', 
    '71166', 
    '71167', 
    '71168', 
    '71169', 
    '71170', 
    '71171', 
    '71172', 
    '71173', 
    '71174', 
    '71175', 
    '71176', 
    '71177', 
    '71178', 
    '71179', 
    '71180', 
    '71181', 
    '71182', 
    '71183', 
    '71184', 
    '71185', 
    '71186', 
    '71187', 
    '71188', 
    '71189', 
    '71190', 
    '71191', 
    '71192', 
    '71193', 
    '71194', 
    '71195', 
    '71196', 
    '71197', 
    '71198', 
    '71199', 
    '71200', 
    '71201', 
    '71202', 
    '71203', 
    '71204', 
    '71205', 
    '71206', 
    '71207', 
    '71208', 
    '71209', 
    '71210', 
    '71211', 
    '71212', 
    '71213', 
    '71214', 
    '71215', 
    '71216', 
    '71217', 
    '71218', 
    '71219', 
    '71220', 
    '71221', 
    '71222', 
    '71223', 
    '71224', 
    '71225', 
    '71226', 
    '71227', 
    '71228', 
    '71229', 
    '71230', 
    '71231', 
    '71232', 
    '71233', 
    '71234', 
    '71235', 
    '71236', 
    '71237', 
    '71238', 
    '71239', 
    '71240', 
    '71241', 
    '71242', 
    '71243', 
    '71244', 
    '71245', 
    '71246', 
    '71247', 
    '71248', 
    '71249', 
    '71250', 
    '71251', 
    '71252', 
    '71253', 
    '71254', 
    '71255', 
    '71256', 
    '71257', 
    '71258', 
    '71259', 
    '71260', 
    '71261', 
    '71262', 
    '71263', 
    '71264', 
    '71265', 
    '71266', 
    '71267', 
    '71268', 
    '71269', 
    '71270', 
    '71271', 
    '71272', 
    '71273', 
    '71274', 
    '71275', 
    '71276', 
    '71277', 
    '71278', 
    '71279', 
    '71280', 
    '71281', 
    '71282', 
    '71283', 
    '71284', 
    '71285', 
    '71286', 
    '71287', 
    '71288', 
    '71289', 
    '71290', 
    '71291', 
    '71292', 
    '71293', 
    '71294', 
    '71295', 
    '71296', 
    '71297', 
    '71298', 
    '71299', 
    '71300', 
    '71301', 
    '71302', 
    '71303', 
    '71304', 
    '71305', 
    '71306', 
    '71307', 
    '71308', 
    '71309', 
    '71310', 
    '71311', 
    '71312', 
    '71313', 
    '71314', 
    '71315', 
    '71316', 
    '71317', 
    '71318'
  )

Query time 0.00458

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` = 1546) and (`cscartdb`.`v`.`product_id` in ('71049','71050','71051','71052','71053','71054','71055','71056','71057','71058','71059','71060','71061','71062','71063','71064','71065','71066','71067','71068','71069','71070','71071','71072','71073','71074','71075','71076','71077','71078','71079','71080','71081','71082','71083','71084','71085','71086','71087','71088','71089','71090','71091','71092','71093','71094','71095','71096','71097','71098','71099','71100','71101','71102','71103','71104','71105','71106','71107','71108','71109','71110','71111','71112','71113','71114','71115','71116','71117','71118','71119','71120','71121','71122','71123','71124','71125','71126','71127','71128','71129','71130','71131','71132','71133','71134','71135','71136','71137','71138','71139','71140','71141','71142','71143','71144','71145','71146','71147','71148','71149','71150','71151','71152','71153','71154','71155','71156','71157','71158','71159','71160','71161','71162','71163','71164','71165','71166','71167','71168','71169','71170','71171','71172','71173','71174','71175','71176','71177','71178','71179','71180','71181','71182','71183','71184','71185','71186','71187','71188','71189','71190','71191','71192','71193','71194','71195','71196','71197','71198','71199','71200','71201','71202','71203','71204','71205','71206','71207','71208','71209','71210','71211','71212','71213','71214','71215','71216','71217','71218','71219','71220','71221','71222','71223','71224','71225','71226','71227','71228','71229','71230','71231','71232','71233','71234','71235','71236','71237','71238','71239','71240','71241','71242','71243','71244','71245','71246','71247','71248','71249','71250','71251','71252','71253','71254','71255','71256','71257','71258','71259','71260','71261','71262','71263','71264','71265','71266','71267','71268','71269','71270','71271','71272','71273','71274','71275','71276','71277','71278','71279','71280','71281','71282','71283','71284','71285','71286','71287','71288','71289','71290','71291','71292','71293','71294','71295','71296','71297','71298','71299','71300','71301','71302','71303','71304','71305','71306','71307','71308','71309','71310','71311','71312','71313','71314','71315','71316','71317','71318')))"
        }
      },
      {
        "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"
          ]
        }
      }
    ]
  }
}