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 (
    4973, 4970, 4972, 4963, 4968, 4967, 4969, 
    4966, 4975, 4974, 4976, 4979, 4978, 
    4980, 4956, 4958, 4957, 4959, 4961
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00156

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": 19,
          "filtered": 100,
          "index_condition": "cscart_images_links.object_type = 'product' and cscart_images_links.`type` = 'M' and cscart_images_links.object_id in (4973,4970,4972,4963,4968,4967,4969,4966,4975,4974,4976,4979,4978,4980,4956,4958,4957,4959,4961)"
        }
      }
    },
    "table": {
      "table_name": "cscart_images",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["image_id"],
      "ref": ["ymonline_livedata.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": ["ymonline_livedata.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))"
    }
  }
}

Result

image_id image_path image_x image_y is_high_res pair_id object_id object_type detailed_id type position alt images_image_id
0 21.5-INCH_IMAC_WITH_RETINA_4K.jpeg 1254 1044 N 7028 4956 product 7774 M 0 7774
0 21.5-INCH_IMAC_WITH_RETINA_4K_i7ti-v4.jpeg 1254 1044 N 7032 4957 product 7778 M 0 7778
0 21.5-INCH_IMAC_WITH_RETINA_4K_mv3t-e4.jpeg 1254 1044 N 7036 4958 product 7782 M 0 7782
0 21.5-INCH_IMAC_WITH_RETINA_4K_ucb7-5u.jpeg 1254 1044 N 7040 4959 product 7786 M 0 7786
0 21.5-INCH_IMAC_WITH_RETINA_4K_isrb-6p.jpeg 1254 1044 N 7048 4961 product 7794 M 0 7794
0 13-INCH_MACBOOK_AIR_SPACE_GRAY.jpeg 904 840 N 7055 4963 product 7801 M 0 7801
0 13-INCH_MACBOOK_AIR_SPACE_GRAY_5chz-i1.jpeg 904 840 N 7067 4966 product 7813 M 0 7813
0 13-INCH_MACBOOK_AIR_GOLD_ax0z-hk.jpeg 904 840 N 7071 4967 product 7817 M 0 7817
0 13-INCH_MACBOOK_AIR_silver_fe65-xs.jpeg 904 840 N 7075 4968 product 7821 M 0 7821
0 13-INCH_MACBOOK_AIR_SPACE_GRAY_x5gu-hz.jpeg 904 840 N 7079 4969 product 7825 M 0 7825
0 13-INCH_MACBOOK_PRO_-_SILVER.jpeg 904 840 N 7083 4970 product 7829 M 0 7829
0 13-INCH_MACBOOK_PRO_-_SILVER_8kt1-x0.jpeg 904 840 N 7091 4972 product 7837 M 0 7837
0 13-INCH_MACBOOK_PRO_-_SPACE_GRAY_pphd-vs.jpeg 904 840 N 7095 4973 product 7841 M 0 7841
0 13-INCH_MACBOOK_PRO_-_SILVER_nf94-tj.jpeg 904 840 N 7099 4974 product 7845 M 0 7845
0 13-INCH_MACBOOK_PRO_-_SPACE_GRAY_56q6-se.jpeg 904 840 N 7103 4975 product 7849 M 0 7849
0 13-INCH_MACBOOK_PRO_-_SILVER_gx6m-mj.jpeg 904 840 N 7107 4976 product 7853 M 0 7853
0 13-INCH_MACBOOK_PRO_-_SILVER_5tfo-lw.jpeg 904 840 N 7115 4978 product 7861 M 0 7861
0 13-INCH_MACBOOK_PRO_-_SPACE_GRAY_yd9y-pl.jpeg 904 840 N 7119 4979 product 7865 M 0 7865
0 13-INCH_MACBOOK_PRO_-_SILVER_x49t-mf.jpeg 904 840 N 7123 4980 product 7869 M 0 7869