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 (
    4, 
    16, 
    15, 
    18, 
    3, 
    17, 
    23, 
    34, 
    37, 
    471, 
    426, 
    143, 
    155, 
    29, 
    514, 
    659, 
    117, 
    12, 
    14, 
    271, 
    249, 
    248, 
    164, 
    217, 
    401, 
    286, 
    520, 
    834, 
    246, 
    310, 
    309, 
    212, 
    523, 
    288, 
    398, 
    379, 
    303, 
    410, 
    411, 
    33, 
    220, 
    445, 
    296, 
    423, 
    441, 
    487, 
    250, 
    547, 
    469, 
    499, 
    561, 
    566, 
    425, 
    568, 
    393, 
    570, 
    612, 
    22, 
    488, 
    578, 
    593, 
    592, 
    594, 
    458, 
    606, 
    609, 
    615, 
    2, 
    613, 
    614, 
    27, 
    797, 
    465, 
    623, 
    622, 
    621, 
    626, 
    627, 
    628, 
    630, 
    565, 
    439, 
    185, 
    639, 
    641, 
    640, 
    775, 
    687, 
    658, 
    724, 
    720, 
    664, 
    676, 
    681, 
    699, 
    705, 
    711, 
    710, 
    709, 
    712, 
    755, 
    736, 
    787, 
    434, 
    417, 
    754, 
    726, 
    760, 
    768, 
    571, 
    769, 
    770, 
    0, 
    774, 
    574, 
    778, 
    725, 
    708, 
    783, 
    784, 
    791, 
    789, 
    804, 
    805, 
    807, 
    816, 
    818, 
    824, 
    823, 
    822, 
    326, 
    832, 
    859, 
    861, 
    862, 
    866, 
    864, 
    879, 
    894, 
    892, 
    163, 
    891, 
    451, 
    881, 
    911, 
    912, 
    418, 
    921, 
    904, 
    865, 
    856, 
    931, 
    938, 
    256, 
    167, 
    902, 
    944, 
    906, 
    927, 
    926, 
    980, 
    677, 
    305, 
    990, 
    989, 
    908, 
    1005, 
    1015, 
    1016, 
    1018, 
    1022, 
    318, 
    1044, 
    799
  ) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  udf_NaturalSortFormat(
    cscart_product_feature_variant_descriptions.variant, 
    10, "."
  ) ASC

Query time 0.00788

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "149.68"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_product_feature_variants",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows_examined_per_scan": 872,
            "rows_produced_per_join": 174,
            "filtered": "19.95",
            "cost_info": {
              "read_cost": "71.38",
              "eval_cost": "17.40",
              "prefix_cost": "88.78",
              "data_read_per_join": "198K"
            },
            "used_columns": [
              "variant_id",
              "color",
              "position"
            ],
            "attached_condition": "(`dbggbern`.`cscart_product_feature_variants`.`variant_id` in (4,16,15,18,3,17,23,34,37,471,426,143,155,29,514,659,117,12,14,271,249,248,164,217,401,286,520,834,246,310,309,212,523,288,398,379,303,410,411,33,220,445,296,423,441,487,250,547,469,499,561,566,425,568,393,570,612,22,488,578,593,592,594,458,606,609,615,2,613,614,27,797,465,623,622,621,626,627,628,630,565,439,185,639,641,640,775,687,658,724,720,664,676,681,699,705,711,710,709,712,755,736,787,434,417,754,726,760,768,571,769,770,0,774,574,778,725,708,783,784,791,789,804,805,807,816,818,824,823,822,326,832,859,861,862,866,864,879,894,892,163,891,451,881,911,912,418,921,904,865,856,931,938,256,167,902,944,906,927,926,980,677,305,990,989,908,1005,1015,1016,1018,1022,318,1044,799))"
          }
        },
        {
          "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": [
              "dbggbern.cscart_product_feature_variants.variant_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 174,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "43.50",
              "eval_cost": "17.40",
              "prefix_cost": "149.68",
              "data_read_per_join": "526K"
            },
            "used_columns": [
              "variant_id",
              "variant",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}