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 = 1392 
  AND v.product_id IN (
    '19079', '56120', '56121', '56122', 
    '56123', '56124', '56125', '56126', 
    '56127', '56128', '56129', '56130', 
    '56131', '56132', '56133', '56134', 
    '56135', '56136', '56137', '56138', 
    '56139', '56140', '56141', '56142', 
    '56143', '56144', '56145', '56146', 
    '56147', '56148', '56149', '56150', 
    '56151', '56152', '56153', '56154', 
    '56155', '56156', '56157', '56158', 
    '56159', '56160', '56161', '56162', 
    '56163', '56164', '56165', '56166', 
    '56167', '56168', '56169', '56170', 
    '56171', '56172', '56173', '56174', 
    '56175', '56176', '56177', '56178', 
    '56179', '56180', '56181', '56182', 
    '56183', '56184', '56185', '56186', 
    '56187', '56188', '56189', '56190', 
    '56191', '56192', '56193', '56194', 
    '56195', '56196', '56197', '56198', 
    '56199', '56200', '56201', '56202', 
    '56203', '56204', '56205', '56206', 
    '56207', '56208'
  )

Query time 0.00312

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "82.15"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "v",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "idx_product_feature_variant_id",
          "used_key_parts": [
            "product_id",
            "feature_id"
          ],
          "key_length": "6",
          "rows_examined_per_scan": 90,
          "rows_produced_per_join": 90,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "10.15",
            "eval_cost": "9.00",
            "prefix_cost": "19.15",
            "data_read_per_join": "69K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ],
          "attached_condition": "((`cscartdb`.`v`.`feature_id` = 1392) and (`cscartdb`.`v`.`product_id` in ('19079','56120','56121','56122','56123','56124','56125','56126','56127','56128','56129','56130','56131','56132','56133','56134','56135','56136','56137','56138','56139','56140','56141','56142','56143','56144','56145','56146','56147','56148','56149','56150','56151','56152','56153','56154','56155','56156','56157','56158','56159','56160','56161','56162','56163','56164','56165','56166','56167','56168','56169','56170','56171','56172','56173','56174','56175','56176','56177','56178','56179','56180','56181','56182','56183','56184','56185','56186','56187','56188','56189','56190','56191','56192','56193','56194','56195','56196','56197','56198','56199','56200','56201','56202','56203','56204','56205','56206','56207','56208')))"
        }
      },
      {
        "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": 90,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "22.50",
            "eval_cost": "9.00",
            "prefix_cost": "50.65",
            "data_read_per_join": "272K"
          },
          "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": 90,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "22.50",
            "eval_cost": "9.00",
            "prefix_cost": "82.15",
            "data_read_per_join": "102K"
          },
          "used_columns": [
            "variant_id",
            "color",
            "position"
          ]
        }
      }
    ]
  }
}