SELECT 
  cscart_images.*, 
  cscart_images_links.*, 
  cscart_common_descriptions.description AS alt, 
  cscart_images.image_id AS images_image_id 
FROM 
  cscart_images_links 
  LEFT JOIN cscart_images ON cscart_images_links.image_id = cscart_images.image_id 
  LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id 
  AND cscart_common_descriptions.object_holder = 'images' 
  AND cscart_common_descriptions.lang_code = 'ru' 
WHERE 
  cscart_images_links.object_type = 'feature_variant' 
  AND cscart_images_links.type = 'V' 
  AND cscart_images_links.object_id IN (
    1253, 1398, 1573, 1565, 1623, 1625, 1616, 
    1580, 1577, 1597, 1618, 1619, 1562, 
    1606, 1621, 1561, 1564, 1559, 1626, 
    1572, 1569, 1571, 1567, 1545, 1566, 
    1627, 1620, 1599, 1595, 1568, 1557, 
    1596, 1581, 1617, 1587, 1607, 1547, 
    1624, 1613, 1609, 1615, 1576, 1558, 
    1579, 1574, 1575, 1605, 1608, 1604, 
    1601, 1603, 1611, 1546, 1570, 1578, 
    1610, 1602, 1600, 1563, 1560, 1593, 
    1582, 1588, 1586, 1592, 1584, 1590, 
    1628, 1585, 1591, 1614, 1622, 1583, 
    1589, 1594, 1612, 1598, 1207, 1549, 
    1551, 1550, 1209, 1548
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00120

JSON explain

{
  "query_block": {
    "select_id": 1,
    "read_sorted_file": {
      "filesort": {
        "sort_key": "cscart_images_links.position, cscart_images_links.pair_id",
        "table": {
          "table_name": "cscart_images_links",
          "access_type": "range",
          "possible_keys": ["object_id"],
          "key": "object_id",
          "key_length": "81",
          "used_key_parts": ["object_id", "object_type", "type"],
          "rows": 83,
          "filtered": 100,
          "index_condition": "cscart_images_links.object_type = 'feature_variant' and cscart_images_links.`type` = 'V' and cscart_images_links.object_id in (1253,1398,1573,1565,1623,1625,1616,1580,1577,1597,1618,1619,1562,1606,1621,1561,1564,1559,1626,1572,1569,1571,1567,1545,1566,1627,1620,1599,1595,1568,1557,1596,1581,1617,1587,1607,1547,1624,1613,1609,1615,1576,1558,1579,1574,1575,1605,1608,1604,1601,1603,1611,1546,1570,1578,1610,1602,1600,1563,1560,1593,1582,1588,1586,1592,1584,1590,1628,1585,1591,1614,1622,1583,1589,1594,1612,1598,1207,1549,1551,1550,1209,1548)"
        }
      }
    },
    "table": {
      "table_name": "cscart_images",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["image_id"],
      "ref": ["noframes_kz_new.cscart_images_links.image_id"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_common_descriptions",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "107",
      "used_key_parts": ["object_id", "lang_code", "object_holder"],
      "ref": ["noframes_kz_new.cscart_images.image_id", "const", "const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(cscart_common_descriptions.object_id = cscart_images.image_id and cscart_common_descriptions.object_holder = 'images' and cscart_common_descriptions.lang_code = 'ru' and trigcond(cscart_images.image_id is not null))"
    }
  }
}