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.category_id != 264 
  AND cscart_categories.parent_id != 264 
  AND cscart_categories.storefront_id IN (0, 1) 
  AND cscart_categories.category_id IN(603, 557, 734) 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00049

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	cscart_categories	range	PRIMARY,c_status,parent,p_category_id	PRIMARY	3		3	Using index condition; 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
734	557	603/557/734	Motor Su Radyatörü	10	A	motor-su-radyatoru	603/557
557	603	603/557	Soğutma Parçaları	130	A	sogutma-parcalari	603
603	0	603	Yedek Parçalar	210	A	yedek-parcalar