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.detailed_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 = 'en' 
WHERE 
  cscart_images_links.object_type = 'product' 
  AND cscart_images_links.type = 'M' 
  AND cscart_images_links.object_id IN (
    298, 428, 430, 427, 232, 520, 521, 522, 
    523, 337, 1562, 1563, 1564, 5473, 1843, 
    1841, 1840, 486, 1842, 405, 1771, 1757, 
    1760, 1765, 1768, 3478, 3479, 3480, 
    5475, 533, 531, 536, 534, 532, 292, 535, 
    309, 550, 554, 555, 556, 3314, 339, 1565, 
    1566, 1567, 1568, 1569, 1570, 5556
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00243

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": 64,
          "filtered": 100,
          "index_condition": "cscart_images_links.object_type = 'product' and cscart_images_links.`type` = 'M' and cscart_images_links.object_id in (298,428,430,427,232,520,521,522,523,337,1562,1563,1564,5473,1843,1841,1840,486,1842,405,1771,1757,1760,1765,1768,3478,3479,3480,5475,533,531,536,534,532,292,535,309,550,554,555,556,3314,339,1565,1566,1567,1568,1569,1570,5556)"
        }
      }
    },
    "table": {
      "table_name": "cscart_images",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["image_id"],
      "ref": ["herbaria_cscart_4_18.cscart_images_links.detailed_id"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_common_descriptions",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "108",
      "used_key_parts": ["object_id", "lang_code", "object_holder"],
      "ref": ["herbaria_cscart_4_18.cscart_images.image_id", "const", "const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(cscart_common_descriptions.object_holder = 'images' and cscart_common_descriptions.lang_code = 'en' and trigcond(cscart_images.image_id is not null))"
    }
  }
}