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 '500/%' 
  AND cscart_categories.company_id = 2 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00086

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		7	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
492	500	500/492	Decorative Stone	10	A	2	decorative-stone	500
867	865	500/865/867	Gips panels	10	A	2	gipspanels	500/865
493	492	500/492/493	Interior Stone	10	A	2	interior-stone	500/492
865	500	500/865	Decorative Paneling	20	A	2	wallpanels	500
494	492	500/492/494	Exterior Stone	20	A	2	exterior-stone	500/492
497	865	500/865/497	Wood panels	20	A	2	woodpanels	500/865
495	492	500/492/495	Brick Slips	30	A	2	brick-slips	500/492
917	865	500/865/917	Upholstered panels	30	A	2	upholstered-panels	500/865
496	492	500/492/496	Natural Stone	40	A	2	natural-stone	500/492
499	492	500/492/499	Accessories	70	A	2	accessories-en-3	500/492