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, 
    7, 
    50, 
    54, 
    238, 
    162, 
    262, 
    265, 
    282, 
    281, 
    120, 
    122, 
    127, 
    149, 
    123, 
    125, 
    234, 
    147, 
    287, 
    10, 
    58, 
    165, 
    487, 
    121, 
    438, 
    439, 
    124, 
    367, 
    146, 
    486, 
    15, 
    98, 
    111, 
    160, 
    158, 
    12, 
    110, 
    166, 
    126, 
    8, 
    170, 
    119, 
    6, 
    211, 
    152, 
    217, 
    220, 
    223, 
    226, 
    227, 
    228, 
    230, 
    231, 
    232, 
    233, 
    237, 
    236, 
    239, 
    41, 
    241, 
    242, 
    40, 
    243, 
    244, 
    245, 
    247, 
    249, 
    248, 
    246, 
    250, 
    251, 
    26, 
    57, 
    260, 
    263, 
    264, 
    258, 
    267, 
    29, 
    273, 
    59, 
    280, 
    276, 
    362, 
    440, 
    22, 
    290, 
    289, 
    294, 
    422, 
    1294, 
    1295, 
    1296, 
    1327, 
    303, 
    304, 
    1305, 
    302, 
    1453, 
    11, 
    9, 
    311, 
    313, 
    35, 
    318, 
    36, 
    320, 
    321, 
    326, 
    240, 
    361, 
    30, 
    1300, 
    1297, 
    1298, 
    1299, 
    1303, 
    376, 
    379, 
    1308, 
    383, 
    270, 
    391, 
    421, 
    419, 
    423, 
    25, 
    424, 
    426, 
    24, 
    429, 
    431, 
    433, 
    513, 
    441, 
    446, 
    447, 
    453, 
    459, 
    460, 
    461, 
    467, 
    462, 
    483, 
    14, 
    473, 
    475, 
    481, 
    476, 
    477, 
    482, 
    492, 
    491, 
    494, 
    39, 
    496, 
    497, 
    164, 
    504, 
    507, 
    19, 
    20, 
    18, 
    508, 
    21, 
    509, 
    1335, 
    1304, 
    479, 
    300, 
    484, 
    478, 
    485, 
    1332, 
    1293, 
    374, 
    457, 
    456, 
    472, 
    480, 
    1348, 
    1349, 
    1351, 
    469, 
    1347, 
    1329, 
    1313, 
    1333, 
    1340, 
    428, 
    1394, 
    1801, 
    1805, 
    1824, 
    1826, 
    1829, 
    1813, 
    1832, 
    1833, 
    1830
  ) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  cscart_product_feature_variant_descriptions.variant ASC

Query time 0.00546

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "179.35"
    },
    "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.66",
            "cost_info": {
              "read_cost": "89.35",
              "eval_cost": "20.00",
              "prefix_cost": "109.35",
              "data_read_per_join": "309K"
            },
            "used_columns": [
              "variant_id",
              "color",
              "position"
            ],
            "attached_condition": "(`test_uchur_k`.`cscart_product_feature_variants`.`variant_id` in (1,7,50,54,238,162,262,265,282,281,120,122,127,149,123,125,234,147,287,10,58,165,487,121,438,439,124,367,146,486,15,98,111,160,158,12,110,166,126,8,170,119,6,211,152,217,220,223,226,227,228,230,231,232,233,237,236,239,41,241,242,40,243,244,245,247,249,248,246,250,251,26,57,260,263,264,258,267,29,273,59,280,276,362,440,22,290,289,294,422,1294,1295,1296,1327,303,304,1305,302,1453,11,9,311,313,35,318,36,320,321,326,240,361,30,1300,1297,1298,1299,1303,376,379,1308,383,270,391,421,419,423,25,424,426,24,429,431,433,513,441,446,447,453,459,460,461,467,462,483,14,473,475,481,476,477,482,492,491,494,39,496,497,164,504,507,19,20,18,508,21,509,1335,1304,479,300,484,478,485,1332,1293,374,457,456,472,480,1348,1349,1351,469,1347,1329,1313,1333,1340,428,1394,1801,1805,1824,1826,1829,1813,1832,1833,1830))"
          }
        },
        {
          "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": "50.00",
              "eval_cost": "20.00",
              "prefix_cost": "179.35",
              "data_read_per_join": "623K"
            },
            "used_columns": [
              "variant_id",
              "variant",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}