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 (
    6497, 6539, 6720, 6787, 7195, 7325, 6480, 
    6991, 7023, 7036, 7323, 7951, 6504, 
    6564, 6858, 7027, 7049, 7085, 7175, 
    7321, 7400, 7441, 7025, 6467
  ) 
  AND cscart_seo_names.type = "p" 
  AND cscart_seo_names.dispatch = "" 
  AND cscart_seo_names.lang_code = 'en'

Query time 0.00124

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "17.71"
    },
    "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": 26,
      "rows_produced_per_join": 26,
      "filtered": "100.00",
      "index_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_seo_names`.`object_id` in (6497,6539,6720,6787,7195,7325,6480,6991,7023,7036,7323,7951,6504,6564,6858,7027,7049,7085,7175,7321,7400,7441,7025,6467)) 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": "15.11",
        "eval_cost": "2.60",
        "prefix_cost": "17.71",
        "data_read_per_join": "44K"
      },
      "used_columns": [
        "name",
        "object_id",
        "type",
        "dispatch",
        "path",
        "lang_code"
      ]
    }
  }
}

Result

product_id seo_name seo_path
6467 braun-shaver-300blk-electric-shaver-for-men 577/578
6480 braun-fs1000-mini-hair-remover-for-women-face-white-and-gold 577/578
6497 braun-bt7420-beard-trimmer-for-men-black-and-silver 577/578
6504 braun-21b-replacement-foil-and-cutter-cassette-for-series-1-silver 577/578
6539 braun-aio5560-all-in-one-hair-and-beard-trimmer-corded-and-cordless-black-and-silver 577/578
6564 braun-silk-expert-pro-3-pl3121-ipl-hair-removal-system-corded 577/578
6720 babyliss-i-pro-230-steam-hair-straightener-with-shine-technology-for-ultimate-straightening 577/473/475
6787 babyliss-hair-straightener-berry-crush-230-10-heat-settings-from-140c-230c-for-use-on-all-hair-types-l-long-length-3m-swivel-cord-long-length-plates-for-fast-smooth-styling 577/473/475
6858 azzaro-the-most-wanted-eau-de-parfum-intense 577/621/622
6991 the-body-shop-soap-almond-milk 577/629/630
7023 elie-saab-le-parfum-eau-de-parfum-women 577/621/623
7025 maybelline-fit-me-matte-poreless-foundation-makeup-120 577/616/617
7027 mdo-simon-ourian-md-the-cleanser 577/629/630
7036 cosrx-the-aha-bha-pha-lha-35-peel-with-panthenol-b5-and-vitamin-b12 577/629/637
7049 sazan-amin-more-curly-mascara 577/616/618
7085 pixi-glow-mist 577/629/637
7175 mario-badescu-aloe-vera-toner 577/629/637
7195 the-body-shop-british-rose-edt 577/621/623
7321 valentino-uomo-intense-edp 577/621/622
7323 valentino-donna-born-in-roma-yellow-dream 577/621/623
7325 valentino-valentina-edp 577/621/623
7400 golden-rose-extreme-gel-shine-nail-color-shade-19-raspberry-pink 577/650/651
7441 golden-rose-extreme-gel-shine-nail-color-shade-43-pearl-brown-gold 577/650/651
7951 white-barn-bath-and-body-works-peachbellini-with-essential-oils-candle 577/621/626