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 (
    8, 
    12, 
    14, 
    42, 
    2, 
    185, 
    41, 
    134, 
    271, 
    53, 
    217, 
    117, 
    137, 
    125, 
    130, 
    141, 
    142, 
    149, 
    156, 
    143, 
    17, 
    150, 
    171, 
    223, 
    184, 
    190, 
    189, 
    214, 
    221, 
    220, 
    225, 
    280, 
    276, 
    234, 
    155, 
    239, 
    163, 
    212, 
    247, 
    251, 
    273, 
    253, 
    282, 
    27, 
    281, 
    332, 
    287, 
    292, 
    465, 
    301, 
    35, 
    303, 
    28, 
    334, 
    375, 
    219, 
    319, 
    318, 
    321, 
    183, 
    322, 
    327, 
    329, 
    288, 
    330, 
    331, 
    339, 
    256, 
    345, 
    306, 
    549, 
    374, 
    394, 
    396, 
    409, 
    428, 
    438, 
    439, 
    441, 
    309, 
    442, 
    443, 
    454, 
    373, 
    469, 
    553, 
    296, 
    4, 
    316, 
    519, 
    483, 
    268, 
    505, 
    186, 
    510, 
    539, 
    499, 
    526, 
    633, 
    531, 
    451, 
    532, 
    543, 
    121, 
    565, 
    566, 
    3, 
    569, 
    573, 
    574, 
    585, 
    594, 
    458, 
    311, 
    423, 
    595, 
    601, 
    615, 
    613, 
    614, 
    617, 
    634, 
    635, 
    265, 
    229, 
    644, 
    645, 
    646, 
    650, 
    563, 
    675, 
    527, 
    681, 
    682, 
    685, 
    688, 
    254, 
    702, 
    703, 
    705, 
    18, 
    713, 
    716, 
    719, 
    725, 
    29, 
    571, 
    769, 
    777, 
    805, 
    16, 
    818, 
    821, 
    831, 
    866, 
    773
  ) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  udf_NaturalSortFormat(
    cscart_product_feature_variant_descriptions.variant, 
    10, "."
  ) ASC

Query time 0.00671

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "143.38"
    },
    "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": 155,
            "filtered": "17.89",
            "cost_info": {
              "read_cost": "73.18",
              "eval_cost": "15.60",
              "prefix_cost": "88.78",
              "data_read_per_join": "177K"
            },
            "used_columns": [
              "variant_id",
              "color",
              "position"
            ],
            "attached_condition": "(`dbggbern`.`cscart_product_feature_variants`.`variant_id` in (8,12,14,42,2,185,41,134,271,53,217,117,137,125,130,141,142,149,156,143,17,150,171,223,184,190,189,214,221,220,225,280,276,234,155,239,163,212,247,251,273,253,282,27,281,332,287,292,465,301,35,303,28,334,375,219,319,318,321,183,322,327,329,288,330,331,339,256,345,306,549,374,394,396,409,428,438,439,441,309,442,443,454,373,469,553,296,4,316,519,483,268,505,186,510,539,499,526,633,531,451,532,543,121,565,566,3,569,573,574,585,594,458,311,423,595,601,615,613,614,617,634,635,265,229,644,645,646,650,563,675,527,681,682,685,688,254,702,703,705,18,713,716,719,725,29,571,769,777,805,16,818,821,831,866,773))"
          }
        },
        {
          "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": 155,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "39.00",
              "eval_cost": "15.60",
              "prefix_cost": "143.38",
              "data_read_per_join": "471K"
            },
            "used_columns": [
              "variant_id",
              "variant",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}