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

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	11	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
555	603	603/555	Motor Parçaları	110	A	motor-parcalari	603
556	603	603/556	Yakıt Parçaları	120	A	yakit-parcalari	603
557	603	603/557	Soğutma Parçaları	130	A	sogutma-parcalari	603
604	603	603/604	Egzoz Parçaları	135	A	egzoz-parcalari	603
558	603	603/558	Şanzıman Parçaları	140	A	sanziman-parcalari	603
559	603	603/559	Süspansiyon Parçaları	150	A	suspansiyon-parcalari	603
560	603	603/560	Direksiyon Parçaları	160	A	direksiyon-parcalari	603
561	603	603/561	Fren Parçaları	170	A	fren-parcalari	603
562	603	603/562	Elektrik Parçaları	180	A	elektrik-parcalari	603
563	603	603/563	Kalorifer ve Klima Parçaları	190	A	kalorifer-ve-klima-parcalari	603
564	603	603/564	Kaporta Parçaları	200	A	kaporta-parcalari	603