SELECT 
  cscart_seo_names.object_id 
FROM 
  cscart_seo_names 
  LEFT JOIN cscart_categories ON cscart_seo_names.object_id = cscart_categories.category_id 
WHERE 
  cscart_seo_names.name = 'sogutma-parcalari' 
  AND cscart_seo_names.type = 'c' 
  AND cscart_categories.status in ('A', 'H') 
  AND cscart_categories.parent_id = 603

Query time 0.00023

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_seo_names ref PRIMARY,name,type name 752 const 1 Using index condition; Using where
1 SIMPLE cscart_categories eq_ref PRIMARY,parent,p_category_id PRIMARY 3 hobilifedt.cscart_seo_names.object_id 1 Using where

Result

object_id
557