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 = 'ru' 
WHERE 
  cscart_product_feature_variants.variant_id IN (
    9081, 6968, 9106, 9085, 6971, 9147, 9163, 
    9107, 6969, 9084, 9052, 6967, 9168, 
    9127, 9086, 9042, 9150, 9167, 9109, 
    9054, 6970, 9148, 9097, 9090, 8815, 
    6966, 9157, 9115, 9064, 9170, 7959, 
    9071, 9153, 9096, 9101, 9171, 9031, 
    9091, 9120, 9035, 9133, 8816, 9105, 
    8813, 9113, 8820, 9116, 8819, 9053, 
    9065, 9151, 8823, 9082, 9162, 8811, 
    9060, 8877, 8876, 9161, 9119, 8875, 
    9128, 9159, 9093, 9083, 9027, 6963, 
    9123, 9080, 8807, 9030, 6965, 9036, 
    9058, 8881, 8809, 9040, 9172, 9079, 
    8858, 8859, 9166, 8860, 8857, 9069, 
    9169, 9118, 9055, 9158, 9028, 9062, 
    9141, 8805, 9155, 9045, 8853, 9140, 
    9077, 9135, 9149, 9122, 9047, 9050, 
    9051, 8812, 9041, 9078, 9061, 8821, 
    9087, 8846, 9063, 9165, 9068, 9056, 
    8850, 9139, 9070, 9044, 9039, 9142, 
    8851, 8822
  ) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  cscart_product_feature_variant_descriptions.variant ASC

Query time 0.00844

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "cscart_product_feature_variants.position, cscart_product_feature_variant_descriptions.variant",
      "temporary_table": {
        "table": {
          "table_name": "cscart_product_feature_variants",
          "access_type": "range",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["variant_id"],
          "rows": 123,
          "filtered": 100,
          "index_condition": "cscart_product_feature_variants.variant_id in (9081,6968,9106,9085,6971,9147,9163,9107,6969,9084,9052,6967,9168,9127,9086,9042,9150,9167,9109,9054,6970,9148,9097,9090,8815,6966,9157,9115,9064,9170,7959,9071,9153,9096,9101,9171,9031,9091,9120,9035,9133,8816,9105,8813,9113,8820,9116,8819,9053,9065,9151,8823,9082,9162,8811,9060,8877,8876,9161,9119,8875,9128,9159,9093,9083,9027,6963,9123,9080,8807,9030,6965,9036,9058,8881,8809,9040,9172,9079,8858,8859,9166,8860,8857,9069,9169,9118,9055,9158,9028,9062,9141,8805,9155,9045,8853,9140,9077,9135,9149,9122,9047,9050,9051,8812,9041,9078,9061,8821,9087,8846,9063,9165,9068,9056,8850,9139,9070,9044,9039,9142,8851,8822)"
        },
        "table": {
          "table_name": "cscart_product_feature_variant_descriptions",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "9",
          "used_key_parts": ["variant_id", "lang_code"],
          "ref": ["admin_test.cscart_product_feature_variants.variant_id", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_product_feature_variant_descriptions.lang_code = 'ru')"
        }
      }
    }
  }
}