SELECT 
  cscart_categories.category_id, 
  cscart_categories.parent_id, 
  cscart_categories.id_path, 
  cscart_category_descriptions.category, 
  cscart_categories.position, 
  cscart_categories.status, 
  cscart_categories.company_id, 
  cscart_categories.storefront_id, 
  cscart_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path 
FROM 
  cscart_categories 
  LEFT JOIN cscart_category_descriptions ON cscart_categories.category_id = cscart_category_descriptions.category_id 
  AND cscart_category_descriptions.lang_code = 'tr' 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = cscart_categories.category_id 
  AND cscart_seo_names.type = 'c' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'tr' 
WHERE 
  1 = 1 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A') 
  AND cscart_categories.parent_id IN (561) 
  AND cscart_categories.id_path LIKE '603/561/%' 
  AND cscart_categories.category_id != 264 
  AND cscart_categories.parent_id != 264 
  AND cscart_categories.storefront_id IN (0, 1) 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00077

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_categories ref PRIMARY,c_status,parent,id_path,p_category_id parent 3 const 15 Using where; Using temporary; Using filesort
1 SIMPLE cscart_category_descriptions eq_ref PRIMARY PRIMARY 9 hobilifedt.cscart_categories.category_id,const 1 Using where
1 SIMPLE cscart_seo_names ref PRIMARY,dispatch PRIMARY 206 hobilifedt.cscart_categories.category_id,const,const,const 1 Using where

Result

category_id parent_id id_path category position status company_id storefront_id seo_name seo_path
691 561 603/561/691 Fren Abs Sensörü 10 A 0 0 fren-abs-sensoru 603/561
692 561 603/561/692 Fren Balata Fişi 20 A 0 0 fren-balata-fisi 603/561
693 561 603/561/693 Fren Balata Sacı 30 A 0 0 fren-balata-saci 603/561
694 561 603/561/694 Fren Balatası 40 A 0 0 fren-balatasi 603/561
695 561 603/561/695 Fren Diski 50 A 0 0 fren-diski 603/561
696 561 603/561/696 Fren Hortumları 60 A 0 0 fren-hortumlari 603/561
697 561 603/561/697 Fren Kaliperi 70 A 0 0 fren-kaliperi 603/561
698 561 603/561/698 Fren Kampana 80 A 0 0 fren-kampana 603/561
699 561 603/561/699 Fren Limitörü 90 A 0 0 fren-limitoru 603/561
700 561 603/561/700 Fren Pabuç Takımı 100 A 0 0 fren-pabuc-takimi 603/561
701 561 603/561/701 Kaliper Tamir Takımı 110 A 0 0 kaliper-tamir-takimi 603/561
702 561 603/561/702 Fren Merkezleri 120 A 0 0 fren-merkezleri 603/561
703 561 603/561/703 Vakum Pompası 130 A 0 0 vakum-pompasi 603/561
704 561 603/561/704 Westinghouse 140 A 0 0 westinghouse 603/561