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 (
    87296, 87297, 85852, 85853, 85862, 85863, 
    91007, 91008, 91019, 91020, 91031, 
    91032, 91044, 91045, 85842, 85843, 
    91004, 91005, 91016, 91017, 91028, 
    91029, 91040, 91041, 91042, 90008, 
    90564, 90570, 90576, 84678, 87304, 
    87305, 87316, 87317, 87328, 87329, 
    87340, 87341, 89818, 89819, 89870, 
    89871, 89942, 89943, 91508, 91509, 
    83083, 83084
  ) 
  AND cscart_seo_names.type = "p" 
  AND cscart_seo_names.dispatch = "" 
  AND cscart_seo_names.lang_code = 'en'

Query time 0.00069

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 (87296,87297,85852,85853,85862,85863,91007,91008,91019,91020,91031,91032,91044,91045,85842,85843,91004,91005,91016,91017,91028,91029,91040,91041,91042,90008,90564,90570,90576,84678,87304,87305,87316,87317,87328,87329,87340,87341,89818,89819,89870,89871,89942,89943,91508,91509,83083,83084)) 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
83083 arno-800mm-floor-standing-2-drawer-vanity-and-thin-edge-basin-en-en-en-en-en-en 7170/7191
83084 arno-800mm-floor-standing-2-drawer-vanity-and-curved-basin-en-en-en-en-en-en 7170/7191
84678 fusion-wall-hung-4-door-vanity-unit-and-polymarble-basin-compact-1000mm-en-en-en-en-en 7170/7191
85842 parade-floor-standing-2-drawer-vanity-unit-with-polymarble-basin-800mm 7170/7191
85843 parade-floor-standing-2-drawer-vanity-unit-with-ceramic-basin-800mm 7170/7191
85852 parade-floor-standing-2-drawer-vanity-basin-unit-and-polymarble-basin-800mm 7170/7191
85853 parade-floor-standing-2-drawer-vanity-basin-unit-and-ceramic-basin-800mm 7170/7191
85862 parade-floor-standing-2-drawer-vanity-basin-unit-and-polymarble-basin-800mm-en 7170/7191
85863 parade-floor-standing-2-drawer-vanity-basin-unit-and-ceramic-basin-800mm-en 7170/7191
87296 lunar-floor-standing-2-drawer-unit-and-polymarble-basin-800mm 7170/7191
87297 lunar-floor-standing-2-drawer-unit-and-ceramic-basin-800mm 7170/7191
87304 lunar-floor-standing-2-drawer-unit-and-polymarble-basin-600mm-en 7170/7191
87305 lunar-floor-standing-2-drawer-unit-and-ceramic-basin-600mm-en 7170/7191
87316 lunar-floor-standing-2-drawer-unit-and-polymarble-basin-600mm-en-en 7170/7191
87317 lunar-floor-standing-2-drawer-unit-and-ceramic-basin-600mm-en-en 7170/7191
87328 lunar-floor-standing-2-drawer-unit-and-polymarble-basin-600mm-en-en-en 7170/7191
87329 lunar-floor-standing-2-drawer-unit-and-ceramic-basin-600mm-en-en-en 7170/7191
87340 lunar-floor-standing-2-drawer-unit-and-polymarble-basin-600mm-en-en-en-en 7170/7191
87341 lunar-floor-standing-2-drawer-unit-and-ceramic-basin-600mm-en-en-en-en 7170/7191
89818 urban-floor-standing-2-door-vanity-unit-with-thin-edge-basin-800mm-en 7170/7191
89819 urban-floor-standing-2-door-vanity-unit-with-curved-basin-800mm-en 7170/7191
89870 urban-floor-standing-2-door-vanity-unit-with-thin-edge-basin-800mm-en-en 7170/7191
89871 urban-floor-standing-2-door-vanity-unit-with-curved-basin-800mm-en-en 7170/7191
89942 urban-800mm-floor-standing-2-door-drawer-unit-and-thin-edge-basin-1-tap-hole 7170/7191/7310
89943 urban-800mm-floor-standing-2-door-drawer-unit-and-curved-basin-1-tap-hole 7170/7191/7310
90008 mayford-floor-standing-3-door-3-drawer-vanity-unit-with-round-basin-1250mm 7170/7191
90564 arno-1200mm-floor-standing-4-door-vanity-and-worktop-en-en-en 7170/7191
90570 arno-1200mm-floor-standing-4-door-vanity-and-worktop-en-en-en-en 7170/7191
90576 arno-1200mm-floor-standing-4-door-vanity-and-worktop-en-en-en-en-en 7170/7191
91004 bexley-floor-standing-2-door-1-shelf-vanity-and-1-tap-hole-fireclay-basin-500mm 7170
91005 bexley-floor-standing-2-door-1-shelf-vanity-and-3-tap-hole-fireclay-basin-500mm 7170
91007 bexley-floor-standing-2-door-1-shelf-vanity-and-1-tap-hole-fireclay-basin-600mm 7170
91008 bexley-floor-standing-2-door-1-shelf-vanity-and-3-tap-hole-fireclay-basin-600mm 7170
91016 bexley-floor-standing-2-door-1-shelf-vanity-and-1-tap-hole-fireclay-basin-500mm-en 7170
91017 bexley-floor-standing-2-door-1-shelf-vanity-and-3-tap-hole-fireclay-basin-500mm-en 7170
91019 bexley-floor-standing-2-door-1-shelf-vanity-and-1-tap-hole-fireclay-basin-600mm-en 7170
91020 bexley-floor-standing-2-door-1-shelf-vanity-and-3-tap-hole-fireclay-basin-600mm-en 7170
91028 bexley-floor-standing-2-door-1-shelf-vanity-and-1-tap-hole-fireclay-basin-500mm-en-en 7170
91029 bexley-floor-standing-2-door-1-shelf-vanity-and-3-tap-hole-fireclay-basin-500mm-en-en 7170
91031 bexley-floor-standing-2-door-1-shelf-vanity-and-1-tap-hole-fireclay-basin-600mm-en-en 7170
91032 bexley-floor-standing-2-door-1-shelf-vanity-and-3-tap-hole-fireclay-basin-600mm-en-en 7170
91040 bexley-floor-standing-2-door-1-shelf-vanity-and-1-tap-hole-fireclay-basin-500mm-en-en-en 7170
91041 bexley-floor-standing-2-door-1-shelf-vanity-and-3-tap-hole-fireclay-basin-500mm-en-en-en 7170
91042 bexley-floor-standing-2-door-1-shelf-vanity-and-0-tap-hole-fireclay-basin-500mm 7170
91044 bexley-floor-standing-2-door-1-shelf-vanity-and-1-tap-hole-fireclay-basin-600mm-en-en-en 7170
91045 bexley-floor-standing-2-door-1-shelf-vanity-and-3-tap-hole-fireclay-basin-600mm-en-en-en 7170
91508 urban-floor-standing-2-door-vanity-and-thin-edge-ceramic-basin-800mm 7170
91509 urban-floor-standing-2-door-vanity-and-curved-ceramic-basin-800mm 7170