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 = 'ar' 
WHERE 
  cscart_product_feature_variants.variant_id IN (
    1526, 
    1561, 
    1572, 
    1539, 
    1586, 
    1610, 
    1583, 
    1607, 
    1600, 
    1562, 
    1515, 
    1524, 
    1552, 
    1684, 
    1563, 
    1571, 
    1585, 
    1558, 
    1584, 
    1682, 
    6252, 
    6250, 
    1599, 
    1711, 
    1687, 
    1615, 
    1618, 
    1554, 
    1696, 
    1613, 
    1695, 
    1634, 
    1635, 
    1636, 
    1637, 
    1553, 
    1633, 
    1614, 
    1619, 
    1686, 
    1611, 
    1690, 
    1631, 
    1632, 
    1612, 
    1516, 
    1564, 
    1559, 
    1560, 
    1517, 
    1525, 
    1557, 
    1556, 
    1660, 
    1659, 
    1518, 
    1642, 
    1703, 
    1609, 
    1707, 
    1638, 
    1702, 
    1681, 
    1570, 
    1540, 
    1608, 
    1595, 
    1555, 
    2137, 
    4941, 
    2136, 
    1938, 
    2175, 
    2115, 
    1776, 
    3421, 
    1596, 
    1913, 
    2460, 
    6360, 
    1755, 
    2315, 
    5517, 
    1598, 
    6255, 
    1914, 
    6253, 
    6254, 
    6256, 
    6257, 
    6258, 
    6271, 
    6285, 
    6284, 
    6290, 
    7801, 
    7800, 
    7047, 
    6995, 
    7064, 
    1750, 
    2307, 
    6130, 
    7627, 
    2077, 
    7534, 
    6971, 
    1754, 
    1912, 
    2758, 
    1587, 
    7512, 
    7514, 
    7521, 
    7529, 
    1784, 
    7535, 
    6680, 
    7542, 
    1606, 
    2243, 
    2443, 
    6991, 
    3656, 
    7010, 
    3814, 
    1674, 
    7191, 
    3809, 
    1753, 
    5991, 
    9077, 
    1565, 
    8393, 
    8395, 
    8471, 
    5563, 
    9493, 
    3015, 
    1591
  ) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  cscart_product_feature_variant_descriptions.variant ASC

Query time 0.00381

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "147.01"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_product_feature_variants",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 140,
            "rows_produced_per_join": 140,
            "filtered": "100.00",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_feature_variants`.`variant_id` in (1526,1561,1572,1539,1586,1610,1583,1607,1600,1562,1515,1524,1552,1684,1563,1571,1585,1558,1584,1682,6252,6250,1599,1711,1687,1615,1618,1554,1696,1613,1695,1634,1635,1636,1637,1553,1633,1614,1619,1686,1611,1690,1631,1632,1612,1516,1564,1559,1560,1517,1525,1557,1556,1660,1659,1518,1642,1703,1609,1707,1638,1702,1681,1570,1540,1608,1595,1555,2137,4941,2136,1938,2175,2115,1776,3421,1596,1913,2460,6360,1755,2315,5517,1598,6255,1914,6253,6254,6256,6257,6258,6271,6285,6284,6290,7801,7800,7047,6995,7064,1750,2307,6130,7627,2077,7534,6971,1754,1912,2758,1587,7512,7514,7521,7529,1784,7535,6680,7542,1606,2243,2443,6991,3656,7010,3814,1674,7191,3809,1753,5991,9077,1565,8393,8395,8471,5563,9493,3015,1591))",
            "cost_info": {
              "read_cost": "84.01",
              "eval_cost": "14.00",
              "prefix_cost": "98.01",
              "data_read_per_join": "159K"
            },
            "used_columns": [
              "variant_id",
              "color",
              "position"
            ]
          }
        },
        {
          "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": [
              "softwarepirmam_hewadelivard_cscart_4.cscart_product_feature_variants.variant_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 140,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "35.00",
              "eval_cost": "14.00",
              "prefix_cost": "147.01",
              "data_read_per_join": "423K"
            },
            "used_columns": [
              "variant_id",
              "variant",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}