SELECT 
  cscart_seo_names.object_id AS product_id, 
  cscart_seo_names.name AS seo_name, 
  cscart_seo_names.path AS seo_path 
FROM 
  cscart_seo_names 
WHERE 
  cscart_seo_names.object_id IN (
    85896, 85897, 85902, 85884, 85903, 85885, 
    85899, 85890, 85898, 85891, 85905, 
    85887, 85904, 85886, 85893, 85895, 
    85892, 85901, 85883, 85889, 85879, 
    85880, 85874, 85875, 85881, 85876, 
    85878, 85873, 85877, 85872, 85914, 
    85915, 85919, 85946, 85947, 85912, 
    85945, 85926, 85923, 85928, 85921, 
    85943, 85944, 85942, 85937, 85938, 
    85936, 85934
  ) 
  AND cscart_seo_names.type = "p" 
  AND cscart_seo_names.dispatch = "" 
  AND cscart_seo_names.lang_code = 'en'

Query time 0.00162

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "21.62"
    },
    "table": {
      "table_name": "cscart_seo_names",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY",
        "dispatch"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "object_id",
        "type",
        "dispatch",
        "lang_code"
      ],
      "key_length": "206",
      "rows_examined_per_scan": 48,
      "rows_produced_per_join": 48,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "16.82",
        "eval_cost": "4.80",
        "prefix_cost": "21.62",
        "data_read_per_join": "81K"
      },
      "used_columns": [
        "name",
        "object_id",
        "type",
        "dispatch",
        "path",
        "lang_code"
      ],
      "attached_condition": "((`nuie_scalesta_net`.`cscart_seo_names`.`object_id` in (85896,85897,85902,85884,85903,85885,85899,85890,85898,85891,85905,85887,85904,85886,85893,85895,85892,85901,85883,85889,85879,85880,85874,85875,85881,85876,85878,85873,85877,85872,85914,85915,85919,85946,85947,85912,85945,85926,85923,85928,85921,85943,85944,85942,85937,85938,85936,85934)) and (`nuie_scalesta_net`.`cscart_seo_names`.`type` = 'p') and (`nuie_scalesta_net`.`cscart_seo_names`.`dispatch` = '') and (`nuie_scalesta_net`.`cscart_seo_names`.`lang_code` = 'en'))"
    }
  }
}

Result

product_id seo_name seo_path
85872 deco-wall-hung-400mm-unit-wc-and-tap 7322/7327
85873 deco-wall-hung-400mm-unit-wc-and-tap-en 7322/7327
85874 deco-wall-hung-400mm-unit-wc-and-tap-en-en 7322/7327
85875 deco-wall-hung-400mm-unit-wc-and-tap-en-en-en 7322/7327
85876 deco-wall-hung-400mm-unit-wc-and-tap-en-en-en-en 7322/7327
85877 deco-floor-standing-400mm-unit-wc-and-tap 7322/7327
85878 deco-floor-standing-400mm-unit-wc-and-tap-en 7322/7327
85879 deco-floor-standing-400mm-unit-wc-and-tap-en-en 7322/7327
85880 deco-floor-standing-400mm-unit-wc-and-tap-en-en-en 7322/7327
85881 deco-floor-standing-400mm-unit-wc-and-tap-en-en-en-en 7322/7327
85883 juno-wall-hung-440mm-wc-and-tap-en 7322/7327
85884 juno-wall-hung-440mm-wc-and-tap-en-en 7322/7327
85885 juno-wall-hung-440mm-wc-and-tap-en-en-en 7322/7327
85886 juno-wall-hung-440mm-wc-and-tap-en-en-en-en 7322/7327
85887 juno-wall-hung-440mm-wc-and-tap-en-en-en-en-en 7322/7327
85889 juno-wall-hung-440mm-wc-and-tap-en-en-en-en-en-en-en 7322/7327
85890 juno-wall-hung-440mm-wc-and-tap-en-en-en-en-en-en-en-en 7322/7327
85891 juno-wall-hung-440mm-wc-and-tap-en-en-en-en-en-en-en-en-en 7322/7327
85892 juno-wall-hung-440mm-wc-and-tap-en-en-en-en-en-en-en-en-en-en 7322/7327
85893 juno-wall-hung-440mm-wc-and-tap-en-en-en-en-en-en-en-en-en-en-en 7322/7327
85895 juno-floor-standing-440mm-wc-and-tap-en 7322/7327
85896 juno-floor-standing-440mm-wc-and-tap-en-en 7322/7327
85897 juno-floor-standing-440mm-wc-and-tap-en-en-en 7322/7327
85898 juno-floor-standing-440mm-wc-and-tap-en-en-en-en 7322/7327
85899 juno-floor-standing-440mm-wc-and-tap-en-en-en-en-en 7322/7327
85901 juno-floor-standing-440mm-wc-and-tap-en-en-en-en-en-en-en 7322/7327
85902 juno-floor-standing-440mm-wc-and-tap-en-en-en-en-en-en-en-en 7322/7327
85903 juno-floor-standing-440mm-wc-and-tap-en-en-en-en-en-en-en-en-en 7322/7327
85904 juno-floor-standing-440mm-wc-and-tap-en-en-en-en-en-en-en-en-en-en 7322/7327
85905 juno-floor-standing-440mm-wc-and-tap-en-en-en-en-en-en-en-en-en-en-en 7322/7327
85912 merit-wall-hung-500mm-unit-wc-and-tap 7322/7327
85914 merit-wall-hung-500mm-unit-wc-and-tap-en-en 7322/7327
85915 merit-wall-hung-500mm-unit-wc-and-tap-en-en-en 7322/7327
85919 merit-wall-hung-500mm-unit-wc-and-tap-en-en-en-en-en-en-en 7322/7327
85921 merit-slimline-wall-hung-500mm-unit-wc-and-tap 7322/7327
85923 merit-slimline-wall-hung-500mm-unit-wc-and-tap-en-en 7322/7327
85926 merit-slimline-wall-hung-500mm-unit-wc-and-tap-en-en-en-en-en 7322/7327
85928 merit-slimline-wall-hung-500mm-unit-wc-and-tap-en-en-en-en-en-en-en 7322/7327
85934 core-wall-hung-500mm-unit-tap-and-wc-en 7322/7327
85936 core-wall-hung-600mm-unit-tap-and-wc 7322/7327
85937 core-wall-hung-600mm-unit-tap-and-wc-en 7322/7327
85938 core-wall-hung-600mm-unit-tap-and-wc-en-en 7322/7327
85942 core-floor-standing-500mm-unit-tap-and-wc 7322/7327
85943 core-floor-standing-500mm-unit-tap-and-wc-en 7322/7327
85944 core-floor-standing-500mm-unit-tap-and-wc-en-en 7322/7327
85945 core-floor-standing-600mm-unit-tap-and-wc 7322/7327
85946 core-floor-standing-600mm-unit-tap-and-wc-en 7322/7327
85947 core-floor-standing-600mm-unit-tap-and-wc-en-en 7322/7327