SELECT 
  cscart_product_feature_variants.variant_id, 
  cscart_product_feature_variants.position, 
  cscart_product_feature_variants.color, 
  cscart_product_feature_variant_descriptions.variant 
FROM 
  cscart_product_feature_variants 
  LEFT JOIN cscart_product_feature_variant_descriptions ON cscart_product_feature_variants.variant_id = cscart_product_feature_variant_descriptions.variant_id 
  AND cscart_product_feature_variant_descriptions.lang_code = 'de' 
WHERE 
  cscart_product_feature_variants.variant_id IN (
    10340, 10343, 14244, 10339, 10583, 10670, 
    10669, 10582, 10368, 14322, 14227, 
    10372, 9981, 10732, 10482, 14381, 10375, 
    10371, 14407, 14419, 10705, 10671, 
    10598, 14441, 10379, 14449, 14453, 
    14454, 10603, 10668, 10584, 14492, 
    14499, 14502, 10370, 10391, 10390, 
    14310, 9851, 9870, 11856, 10330, 9876, 
    10830, 10836, 10818, 11976, 11936, 
    10877, 10892, 10900, 12007, 10870, 
    10964, 10942, 10949, 10986, 11686, 
    10852, 11029, 10878, 11878, 10787, 
    11107, 10885, 10786, 11972, 10897, 
    10990, 10953, 13914, 13915
  ) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  cscart_product_feature_variant_descriptions.variant ASC

Query time 0.00146

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "259.21"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_product_feature_variants",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 72,
            "rows_produced_per_join": 72,
            "filtered": "100.00",
            "index_condition": "(`danishecarter_latest`.`cscart_product_feature_variants`.`variant_id` in (10340,10343,14244,10339,10583,10670,10669,10582,10368,14322,14227,10372,9981,10732,10482,14381,10375,10371,14407,14419,10705,10671,10598,14441,10379,14449,14453,14454,10603,10668,10584,14492,14499,14502,10370,10391,10390,14310,9851,9870,11856,10330,9876,10830,10836,10818,11976,11936,10877,10892,10900,12007,10870,10964,10942,10949,10986,11686,10852,11029,10878,11878,10787,11107,10885,10786,11972,10897,10990,10953,13914,13915))",
            "cost_info": {
              "read_cost": "158.41",
              "eval_cost": "14.40",
              "prefix_cost": "172.81",
              "data_read_per_join": "190K"
            },
            "used_columns": [
              "variant_id",
              "color",
              "position"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_product_feature_variant_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "danishecarter_latest.cscart_product_feature_variants.variant_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 72,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "72.00",
              "eval_cost": "14.40",
              "prefix_cost": "259.21",
              "data_read_per_join": "217K"
            },
            "used_columns": [
              "variant_id",
              "variant",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}