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 (
    10009, 10012, 10420, 10428, 10406, 10480, 
    10486, 10545, 10488, 10384, 10553, 
    10482, 10013, 10381, 10467, 10551, 
    10011, 10555, 10007, 10008, 10010, 
    10357, 10358, 10378
  ) 
  AND cscart_seo_names.type = "p" 
  AND cscart_seo_names.dispatch = "" 
  AND cscart_seo_names.lang_code = 'en'

Query time 0.00140

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "16.81"
    },
    "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": 24,
      "rows_produced_per_join": 24,
      "filtered": "100.00",
      "index_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_seo_names`.`object_id` in (10009,10012,10420,10428,10406,10480,10486,10545,10488,10384,10553,10482,10013,10381,10467,10551,10011,10555,10007,10008,10010,10357,10358,10378)) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_seo_names`.`type` = 'p') and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_seo_names`.`dispatch` = '') and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_seo_names`.`lang_code` = 'en'))",
      "cost_info": {
        "read_cost": "14.41",
        "eval_cost": "2.40",
        "prefix_cost": "16.81",
        "data_read_per_join": "40K"
      },
      "used_columns": [
        "name",
        "object_id",
        "type",
        "dispatch",
        "path",
        "lang_code"
      ]
    }
  }
}

Result

product_id seo_name seo_path
10007 hausberg-electric-sandwich-maker 372/466/573
10008 hausberg-toaster 372/466/573
10009 hausberg-electric-toaster-machine 372/466/573
10010 hausberg-electric-toaster-machine-white 372/466/573
10011 hausberg-electric-mixer 372/466/574
10012 hausberg-electric-mixer-hb-4120ng 372/466/574
10013 hausberg-rechargeable-hand-mixer 372/466/574
10357 hausberg-hb-900ab-popcorn-maker 372/466
10358 hausberg-hb-910rs-popcorn-maker 372/466
10378 hausberg-hb-3815in-kettle-800-watt 372/466/575
10381 hausberg-hb-3622rs-kettle-2-0-l 372/466/575
10384 hausberg-hb-3640in-tea-maker 372/466/575
10406 hausberg-electric-blender-set-with-jar 372/466/574
10420 hausberg-hb-7675ng-hand-blender 372/466/574
10428 hausberg-hb-2365-electric-fryer 372/466/467
10467 hausberg-hb-6030ng-hot-water-dispenser-with-tea-bar 372/466/575
10480 hausberg-electric-professional-sandwich-maker-and-grill 372/466/573
10482 hausberg-electric-stand-mixer 372/466/574
10486 hausberg-electric-mixer-en 372/466/574
10488 hausberg-electric-steel-blender-set-with-glass-jar-hb-7764in 372/466/574
10545 hausberg-electric-midi-oven-with-fan-and-double-glass 372/466/579
10551 hausberg-single-burner-electrical-hot-plate 372/466
10553 hausberg-double-burner-electrical-hot-plate 372/466
10555 hausberg-electric-ice-maker 372/466/655