Sandbox

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 (554) 
  AND cscart_categories.id_path LIKE '554/%' 
  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.00053

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	12	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	seo_name	seo_path
565	554	554/565	Yağ Filtresi	10	A	yag-filtresi	554
566	554	554/566	Hava Filtresi	20	A	hava-filtresi	554
567	554	554/567	Yakıt Filtresi	30	A	yakit-filtresi	554
568	554	554/568	Polen Filtresi	40	A	polen-filtresi	554
569	554	554/569	Hidrolik Filtresi	50	A	hidrolik-filtresi	554
570	554	554/570	Buji	60	A	buji	554
571	554	554/571	V Kayışı	70	A	v-kayisi	554
572	554	554/572	Triger Set	80	A	triger-set	554
573	554	554/573	Termostat / Conta	90	A	termostat-conta	554
574	554	554/574	Karter Tapası	100	A	karter-tapasi	554
575	554	554/575	Silecek Lastiği	110	A	silecek-lastigi	554
576	554	554/576	Devirdaim (Su Pompası)	120	A	devirdaim-su-pompasi	554
577	554	554/577	Fren Balata Seti	130	A	fren-balata-seti	554