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

Query time 0.00093

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 59 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
841 834 741/834/841 Escaliers escamotables 10 A 3 fakro-escaliers-escamotables 741/834
877 876 741/876/877 FAKRO portillon de comble 10 A 3 fakro-portillon-de-comble 741/876
780 760 741/760/780 Parquet Barlinek 10 A 3 parquet-barlinek 741/760
871 870 741/870/871 Peinture blanche mur et plafond 10 A 3 peinture-blanche-mur-et-plafond 741/870
759 741 741/759 Plaque de plâtre 10 A 3 plaque-de-platre 741
778 759 741/759/778 Plaques de plâtre 10 A 3 plaques-de-platre 741/759
786 759 741/759/786 Panneaux OSB 20 A 3 panneaux-osb 741/759
870 741 741/870 Peinture intérieure 20 A 3 peinture-interieure 741
793 759 741/759/793 Profilé 20 A 3 profile 741/759
804 759 741/759/804 Accessoires 30 A 3 accessoires-interieur 741/759
760 741 741/760 Plancher 30 A 3 plancher 741
803 759 741/759/803 Produits de gypse 30 A 3 produits-de-gypse 741/759
834 741 741/834 Escaliers escamotables 40 A 3 escaliers-escamotables 741
876 741 741/876 Portillon de comble 50 A 3 portillon-de-comble 741