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_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 = 'en' 
  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 = 'en' 
  AND cscart_seo_names.company_id = cscart_categories.company_id 
WHERE 
  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.id_path LIKE '501/%' 
  AND cscart_categories.company_id = 1 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00079

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	cscart_categories	range	c_status,id_path,idx_id_path	id_path	767		11	Using index condition; Using where; Using temporary; Using filesort
1	SIMPLE	cscart_category_descriptions	eq_ref	PRIMARY	PRIMARY	9	mahm3t_cs443.cscart_categories.category_id,const	1	Using where
1	SIMPLE	cscart_seo_names	eq_ref	PRIMARY,dispatch	PRIMARY	210	mahm3t_cs443.cscart_categories.category_id,const,const,const,const	1	Using where

Result

category_id	parent_id	id_path	category	position	status	company_id	seo_name	seo_path
502	501	501/502	Decorative Stone	10	A	1	decorative-stone	501
864	863	501/863/864	Gips panels	10	A	1	gips-panels	501/863
503	502	501/502/503	Interior Stone	10	A	1	interior-stone	501/502
863	501	501/863	Decorative Paneling	20	A	1	decorative-paneling	501
504	502	501/502/504	Exterior Stone	20	A	1	exterior-stone	501/502
505	502	501/502/505	Brick Slips	30	A	1	brick-slips	501/502
506	502	501/502/506	Natural Stone	40	A	1	natural-stone	501/502
507	863	501/863/507	Wood panels	50	A	1	wood-panels	501/863
509	502	501/502/509	Accessories	70	A	1	accessories-en-2	501/502
853	502	501/502/853	Corners	80	A	1	corners	501/502
916	863	501/863/916	Upholstered panels	210	A	1	upholstered-panels	501/863