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 (
    85096, 83555, 83556, 86490, 86766, 85304, 
    85401, 85452, 85503, 86618, 86687, 
    85353, 85355, 83040, 90876, 90955, 
    83350, 83351, 83352, 83353, 85054, 
    85055, 85140, 85141, 85186, 85187, 
    85232, 85233, 82812, 82814
  ) 
  AND cscart_seo_names.type = "p" 
  AND cscart_seo_names.dispatch = "" 
  AND cscart_seo_names.lang_code = 'en'

Query time 0.00126

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.52"
    },
    "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": 30,
      "rows_produced_per_join": 30,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "10.52",
        "eval_cost": "3.00",
        "prefix_cost": "13.52",
        "data_read_per_join": "50K"
      },
      "used_columns": [
        "name",
        "object_id",
        "type",
        "dispatch",
        "path",
        "lang_code"
      ],
      "attached_condition": "((`nuie_scalesta_net`.`cscart_seo_names`.`object_id` in (85096,83555,83556,86490,86766,85304,85401,85452,85503,86618,86687,85353,85355,83040,90876,90955,83350,83351,83352,83353,85054,85055,85140,85141,85186,85187,85232,85233,82812,82814)) 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
82812 arno-600mm-wall-hung-1-drawer-vanity-and-thin-edge-basin-en-en-en 7170/7171
82814 arno-600mm-wall-hung-1-drawer-vanity-and-curved-basin-en-en-en 7170/7171
83040 arno-600mm-wall-hung-2-door-vanity-and-minimalist-basin-en-en-en-en-en-en 7170/7171
83350 athena-wall-hung-1-drawer-vanity-with-ceramic-mid-edge-basin-and-handle-600mm-en-en-en-en 7170/7171
83351 athena-wall-hung-1-drawer-vanity-with-ceramic-mid-edge-basin-and-handle-600mm-en-en-en-en-en 7170/7171
83352 athena-wall-hung-1-drawer-vanity-with-ceramic-mid-edge-basin-and-handle-600mm-en-en-en-en-en-en 7170/7171
83353 athena-wall-hung-1-drawer-vanity-with-ceramic-mid-edge-basin-and-handle-600mm-en-en-en-en-en-en-en 7170/7171
83555 athena-wall-hung-single-drawer-vanity-with-thin-edge-1-tap-hole-basin-500mm-en-en-en-en-en 7170/7171
83556 athena-wall-hung-single-drawer-vanity-with-curved-1-tap-hole-basin-500mm-en-en-en-en-en 7170/7171
85054 fluted-500mm-wall-hung-single-drawer-vanity-and-mid-edge-basin 7170/7171
85055 fluted-500mm-wall-hung-single-drawer-vanity-and-minimalist-basin 7170/7171
85096 fluted-500mm-wall-hung-single-drawer-vanity-and-thin-edge-basin-en 7170/7171
85140 fluted-500mm-wall-hung-single-drawer-vanity-and-mid-edge-basin-en-en 7170/7171
85141 fluted-500mm-wall-hung-single-drawer-vanity-and-minimalist-basin-en-en 7170/7171
85186 fluted-500mm-wall-hung-single-drawer-vanity-and-mid-edge-basin-en-en-en 7170/7171
85187 fluted-500mm-wall-hung-single-drawer-vanity-and-minimalist-basin-en-en-en 7170/7171
85232 fluted-500mm-wall-hung-single-drawer-vanity-and-mid-edge-basin-en-en-en-en 7170/7171
85233 fluted-500mm-wall-hung-single-drawer-vanity-and-minimalist-basin-en-en-en-en 7170/7171
85304 deco-600mm-wall-hung-single-drawer-vanity-and-mid-edge-basin 7170/7171
85353 deco-600mm-wall-hung-single-drawer-vanity-and-thin-edge-basin-en 7170/7171
85355 deco-600mm-wall-hung-single-drawer-vanity-and-curved-basin-en 7170/7171
85401 deco-600mm-wall-hung-single-drawer-vanity-and-mid-edge-basin-en-en 7170/7171
85452 deco-600mm-wall-hung-single-drawer-vanity-and-mid-edge-basin-en-en-en 7170/7171
85503 deco-600mm-wall-hung-single-drawer-vanity-and-mid-edge-basin-en-en-en-en 7170/7171
86490 juno-600mm-wall-hung-2-door-vanity-and-mid-edge-1-tap-hole-basin-en 7170/7171
86618 juno-600mm-wall-hung-single-drawer-vanity-and-minimalist-1-tap-hole-basin-en-en-en 7170/7171
86687 juno-600mm-wall-hung-single-drawer-vanity-and-minimalist-1-tap-hole-basin-en-en-en-en 7170/7171
86766 juno-wall-hung-2-door-vanity-basin-unit-and-mid-edge-ceramic-basin-600mm 7170/7171
90876 arno-wall-hung-2-door-vanity-and-minimalist-ceramic-basin-600mm 7170
90955 arno-wall-hung-2-door-vanity-and-minimalist-ceramic-basin-600mm-en 7170