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 '257/%' 
  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.00078

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 8 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
510 280 257/280/510 Traditional plasters 5 A 2 traditional-plasters 257/280
518 280 257/280/518 Gypsum plasters 7 A 2 gypsum-plasters 257/280
384 257 257/384 Bricks 10 A 2 bricks-en 257
279 384 257/384/279 Cellular concrete 10 A 2 cellular-concrete-en 257/384
371 280 257/280/371 Screeds 10 A 2 floor-finish-en 257/280
395 394 257/394/395 Wire mesh reinforcement 10 A 2 wire-mesh-reinforcement-en 257/394
405 384 257/384/405 Calcium silicate blocks 15 A 2 calcium-silicate-blocks-en 257/384
372 280 257/280/372 Adhesives for tiles 20 A 2 adhesives-for-teils 257/280
410 384 257/384/410 Leca 20 A 2 leca 257/384
280 257 257/280 Mortar & adhesive 20 A 2 mortar-and-adhesive-en 257
394 257 257/394 Steel 30 A 2 steel-en 257
884 384 257/384/884 Tools 30 A 2 tools 257/384
406 280 257/280/406 Cement 40 A 2 cement-en-2 257/280
894 280 257/280/894 Hydroisolation 50 A 2 hydroisolation-en 257/280