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 = 'en' 
WHERE 
  cscart_images_links.object_type = 'product' 
  AND cscart_images_links.type = 'A' 
  AND cscart_images_links.object_id IN (
    38872, 38873, 64701, 65108, 65109, 65497, 
    65827, 65870, 65878, 65880, 65885, 
    65887, 65890, 65891, 65892, 65893, 
    65894, 65896, 65902, 65903, 65904, 
    65905, 65906, 65907
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00142

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "629.61"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "158.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_images_links",
            "access_type": "range",
            "possible_keys": [
              "object_id",
              "idx_object_type_type_position"
            ],
            "key": "object_id",
            "used_key_parts": [
              "object_id",
              "object_type",
              "type"
            ],
            "key_length": "81",
            "rows_examined_per_scan": 158,
            "rows_produced_per_join": 158,
            "filtered": "100.00",
            "index_condition": "((`cscart_db`.`cscart_images_links`.`object_type` = 'product') and (`cscart_db`.`cscart_images_links`.`type` = 'A') and (`cscart_db`.`cscart_images_links`.`object_id` in (38872,38873,64701,65108,65109,65497,65827,65870,65878,65880,65885,65887,65890,65891,65892,65893,65894,65896,65902,65903,65904,65905,65906,65907)))",
            "cost_info": {
              "read_cost": "168.29",
              "eval_cost": "15.80",
              "prefix_cost": "184.09",
              "data_read_per_join": "14K"
            },
            "used_columns": [
              "pair_id",
              "object_id",
              "object_type",
              "image_id",
              "detailed_id",
              "type",
              "position"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_images",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "image_id"
            ],
            "key_length": "4",
            "ref": [
              "cscart_db.cscart_images_links.image_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 158,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "131.04",
              "eval_cost": "15.80",
              "prefix_cost": "330.93",
              "data_read_per_join": "120K"
            },
            "used_columns": [
              "image_id",
              "image_path",
              "image_x",
              "image_y",
              "is_high_res"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_common_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "lang_code",
              "object_holder"
            ],
            "key_length": "107",
            "ref": [
              "cscart_db.cscart_images.image_id",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 158,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "124.88",
              "eval_cost": "15.80",
              "prefix_cost": "471.61",
              "data_read_per_join": "93K"
            },
            "used_columns": [
              "object_id",
              "description",
              "lang_code",
              "object_holder"
            ],
            "attached_condition": "<if>(is_not_null_compl(cscart_common_descriptions), (`cscart_db`.`cscart_common_descriptions`.`object_id` = `cscart_db`.`cscart_images`.`image_id`), true)"
          }
        }
      ]
    }
  }
}

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 169709 38872 product 171058 A 1
0 169714 38873 product 171063 A 1
0 175114 65108 product 176460 A 1
0 175118 65109 product 176464 A 1
0 177299 65497 product 178645 A 1
0 179291 65870 product 180637 A 1
0 179336 65885 product 180682 A 1
0 179342 65887 product 180688 A 1
0 179353 65890 product 180699 A 1
0 179564 65891 product 180910 A 1
0 179573 65892 product 180919 A 1
0 179582 65893 product 180928 A 1
0 179591 65894 product 180937 A 1
0 179609 65896 product 180955 A 1
0 179663 65902 product 181009 A 1
0 179672 65903 product 181018 A 1
0 179681 65904 product 181027 A 1
0 179690 65905 product 181036 A 1
0 179699 65906 product 181045 A 1
0 179708 65907 product 181054 A 1
0 184307 65878 product 185653 A 1
0 184437 65880 product 185783 A 1
0 169710 38872 product 171059 A 2
0 169715 38873 product 171064 A 2
0 175115 65108 product 176461 A 2
0 175119 65109 product 176465 A 2
0 177300 65497 product 178646 A 2
0 179292 65870 product 180638 A 2
0 179337 65885 product 180683 A 2
0 179343 65887 product 180689 A 2
0 179354 65890 product 180700 A 2
0 179565 65891 product 180911 A 2
0 179574 65892 product 180920 A 2
0 179583 65893 product 180929 A 2
0 179592 65894 product 180938 A 2
0 179610 65896 product 180956 A 2
0 179664 65902 product 181010 A 2
0 179673 65903 product 181019 A 2
0 179682 65904 product 181028 A 2
0 179691 65905 product 181037 A 2
0 179700 65906 product 181046 A 2
0 179709 65907 product 181055 A 2
0 184308 65878 product 185654 A 2
0 184438 65880 product 185784 A 2
0 195765 65827 product 197103 A 2
0 169711 38872 product 171060 A 3
0 169716 38873 product 171065 A 3
0 175116 65108 product 176462 A 3
0 175120 65109 product 176466 A 3
0 177301 65497 product 178647 A 3
0 179338 65885 product 180684 A 3
0 179344 65887 product 180690 A 3
0 179566 65891 product 180912 A 3
0 179575 65892 product 180921 A 3
0 179584 65893 product 180930 A 3
0 179593 65894 product 180939 A 3
0 179611 65896 product 180957 A 3
0 179665 65902 product 181011 A 3
0 179674 65903 product 181020 A 3
0 179683 65904 product 181029 A 3
0 179692 65905 product 181038 A 3
0 179701 65906 product 181047 A 3
0 179710 65907 product 181056 A 3
0 184309 65878 product 185655 A 3
0 184439 65880 product 185785 A 3
0 195766 65827 product 197104 A 3
0 169712 38872 product 171061 A 4
0 169717 38873 product 171066 A 4
0 177302 65497 product 178648 A 4
0 179345 65887 product 180691 A 4
0 179567 65891 product 180913 A 4
0 179576 65892 product 180922 A 4
0 179585 65893 product 180931 A 4
0 179594 65894 product 180940 A 4
0 179612 65896 product 180958 A 4
0 179666 65902 product 181012 A 4
0 179675 65903 product 181021 A 4
0 179684 65904 product 181030 A 4
0 179693 65905 product 181039 A 4
0 179702 65906 product 181048 A 4
0 179711 65907 product 181057 A 4
0 184310 65878 product 185656 A 4
0 184440 65880 product 185786 A 4
0 189416 65885 product 190754 A 4
0 195767 65827 product 197105 A 4
0 179568 65891 product 180914 A 5
0 179577 65892 product 180923 A 5
0 179586 65893 product 180932 A 5
0 179595 65894 product 180941 A 5
0 179613 65896 product 180959 A 5
0 179667 65902 product 181013 A 5
0 179676 65903 product 181022 A 5
0 179685 65904 product 181031 A 5
0 179694 65905 product 181040 A 5
0 179703 65906 product 181049 A 5
0 179712 65907 product 181058 A 5
0 184311 65878 product 185657 A 5
0 184441 65880 product 185787 A 5
0 189407 65887 product 190745 A 5
0 189417 65885 product 190755 A 5
0 195768 65827 product 197106 A 5
0 179569 65891 product 180915 A 6
0 179578 65892 product 180924 A 6
0 179587 65893 product 180933 A 6
0 179596 65894 product 180942 A 6
0 179614 65896 product 180960 A 6
0 179668 65902 product 181014 A 6
0 179677 65903 product 181023 A 6
0 179686 65904 product 181032 A 6
0 179695 65905 product 181041 A 6
0 179704 65906 product 181050 A 6
0 179713 65907 product 181059 A 6
0 184312 65878 product 185658 A 6
0 184442 65880 product 185788 A 6
0 189408 65887 product 190746 A 6
0 189418 65885 product 190756 A 6
0 195769 65827 product 197107 A 6
0 179570 65891 product 180916 A 7
0 179579 65892 product 180925 A 7
0 179588 65893 product 180934 A 7
0 179597 65894 product 180943 A 7
0 179615 65896 product 180961 A 7
0 179669 65902 product 181015 A 7
0 179678 65903 product 181024 A 7
0 179687 65904 product 181033 A 7
0 179696 65905 product 181042 A 7
0 179705 65906 product 181051 A 7
0 179714 65907 product 181060 A 7
0 184335 65878 product 185681 A 7
0 184443 65880 product 185789 A 7
0 189409 65887 product 190747 A 7
0 189419 65885 product 190757 A 7
0 195770 65827 product 197108 A 7
0 179571 65891 product 180917 A 8
0 179580 65892 product 180926 A 8
0 179589 65893 product 180935 A 8
0 179598 65894 product 180944 A 8
0 179616 65896 product 180962 A 8
0 179670 65902 product 181016 A 8
0 179679 65903 product 181025 A 8
0 179688 65904 product 181034 A 8
0 179697 65905 product 181043 A 8
0 179706 65906 product 181052 A 8
0 179715 65907 product 181061 A 8
0 184314 65878 product 185660 A 8
0 184444 65880 product 185790 A 8
0 189410 65887 product 190748 A 8
0 189420 65885 product 190758 A 8
0 195771 65827 product 197109 A 8
0 184315 65878 product 185661 A 9
0 184445 65880 product 185791 A 9
0 189411 65887 product 190749 A 9
0 184316 65878 product 185662 A 10
0 184446 65880 product 185792 A 10
0 189412 65887 product 190750 A 10
0 189413 65887 product 190751 A 11
0 189414 65887 product 190752 A 12