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_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 = 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 '223/%' 
  AND cscart_categories.storefront_id IN (0, 1) 
  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.00046

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

Result

category_id	parent_id	id_path	category	position	status	company_id	storefront_id	seo_name	seo_path
224	223	223/224	Men's Clothing	10	A	1	1	mens-clothing	223
225	223	223/225	Women's Clothing	20	A	1	1	womens-clothing	223
226	223	223/226	Shoes	30	A	1	1	shoes	223
227	223	223/227	Watches & Jewelry	40	A	1	1	watches-and-jewelry	223