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 (
    1, 
    6, 
    58, 
    165, 
    244, 
    121, 
    122, 
    152, 
    125, 
    146, 
    147, 
    98, 
    217, 
    211, 
    232, 
    233, 
    120, 
    234, 
    238, 
    239, 
    127, 
    149, 
    123, 
    237, 
    236, 
    231, 
    230, 
    250, 
    223, 
    170, 
    242, 
    226, 
    50, 
    251, 
    249, 
    248, 
    246, 
    245, 
    40, 
    247, 
    41, 
    243, 
    241, 
    227, 
    228, 
    220, 
    7, 
    111, 
    421, 
    422, 
    462, 
    126, 
    12, 
    467, 
    483, 
    475, 
    481, 
    10, 
    11, 
    313, 
    14, 
    476, 
    473, 
    485, 
    124, 
    486, 
    487, 
    438, 
    439, 
    367, 
    8, 
    460, 
    461, 
    484, 
    478, 
    479, 
    472, 
    480, 
    477, 
    482, 
    469, 
    26, 
    1294, 
    1295, 
    1304, 
    263, 
    1296, 
    1335, 
    1332, 
    1327, 
    1293, 
    57, 
    492, 
    491, 
    164, 
    3, 
    32, 
    489, 
    366, 
    494, 
    240, 
    39, 
    496, 
    497, 
    15, 
    162, 
    265, 
    282, 
    504, 
    270, 
    507, 
    19, 
    21, 
    320, 
    509, 
    262, 
    508, 
    287, 
    18, 
    20, 
    160, 
    158, 
    1300, 
    1826, 
    1349, 
    1829, 
    289, 
    1813, 
    54, 
    281, 
    22, 
    290, 
    294, 
    300, 
    303, 
    304, 
    110, 
    1305, 
    302, 
    1453, 
    9, 
    311, 
    43, 
    154, 
    155, 
    148, 
    1462, 
    1463, 
    1466, 
    1465, 
    1832, 
    1833, 
    1830, 
    267, 
    25, 
    424, 
    426, 
    24, 
    414, 
    419, 
    423, 
    59, 
    1347, 
    1351, 
    1348, 
    428, 
    31, 
    299, 
    296, 
    298, 
    260, 
    264, 
    258, 
    453, 
    457, 
    456, 
    459, 
    33, 
    119, 
    1468, 
    1396, 
    1470, 
    35, 
    318, 
    36, 
    326, 
    1333, 
    1394, 
    321, 
    440, 
    1801, 
    42, 
    173, 
    172, 
    176, 
    44, 
    503, 
    502, 
    166
  ) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  cscart_product_feature_variant_descriptions.variant ASC

Query time 0.00516

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "179.00"
    },
    "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": 1072,
            "rows_produced_per_join": 199,
            "filtered": "18.56",
            "cost_info": {
              "read_cost": "89.45",
              "eval_cost": "19.90",
              "prefix_cost": "109.35",
              "data_read_per_join": "307K"
            },
            "used_columns": [
              "variant_id",
              "color",
              "position"
            ],
            "attached_condition": "(`test_uchur_k`.`cscart_product_feature_variants`.`variant_id` in (1,6,58,165,244,121,122,152,125,146,147,98,217,211,232,233,120,234,238,239,127,149,123,237,236,231,230,250,223,170,242,226,50,251,249,248,246,245,40,247,41,243,241,227,228,220,7,111,421,422,462,126,12,467,483,475,481,10,11,313,14,476,473,485,124,486,487,438,439,367,8,460,461,484,478,479,472,480,477,482,469,26,1294,1295,1304,263,1296,1335,1332,1327,1293,57,492,491,164,3,32,489,366,494,240,39,496,497,15,162,265,282,504,270,507,19,21,320,509,262,508,287,18,20,160,158,1300,1826,1349,1829,289,1813,54,281,22,290,294,300,303,304,110,1305,302,1453,9,311,43,154,155,148,1462,1463,1466,1465,1832,1833,1830,267,25,424,426,24,414,419,423,59,1347,1351,1348,428,31,299,296,298,260,264,258,453,457,456,459,33,119,1468,1396,1470,35,318,36,326,1333,1394,321,440,1801,42,173,172,176,44,503,502,166))"
          }
        },
        {
          "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": [
              "test_uchur_k.cscart_product_feature_variants.variant_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 199,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "49.75",
              "eval_cost": "19.90",
              "prefix_cost": "179.00",
              "data_read_per_join": "620K"
            },
            "used_columns": [
              "variant_id",
              "variant",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}