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, 
  cscart_categories.ab__lc_catalog_image_control, 
  cscart_category_descriptions.ab__custom_category_h1 
FROM 
  cscart_categories 
  LEFT JOIN cscart_category_descriptions ON cscart_categories.category_id = cscart_category_descriptions.category_id 
  AND cscart_category_descriptions.lang_code = 'ru' 
  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 = 'ru' 
  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.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.00336

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.70"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "ALL",
            "possible_keys": [
              "c_status"
            ],
            "rows_examined_per_scan": 59,
            "rows_produced_per_join": 1,
            "filtered": "1.69",
            "cost_info": {
              "read_cost": "6.90",
              "eval_cost": "0.10",
              "prefix_cost": "7.00",
              "data_read_per_join": "4K"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "company_id",
              "usergroup_ids",
              "status",
              "position",
              "is_trash",
              "ab__lc_catalog_image_control",
              "storefront_id"
            ],
            "attached_condition": "((`testdobroded`.`cscart_categories`.`company_id` = 1) and ((`testdobroded`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`testdobroded`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`testdobroded`.`cscart_categories`.`usergroup_ids`))) and (`testdobroded`.`cscart_categories`.`status` = 'A') and (`testdobroded`.`cscart_categories`.`storefront_id` in (0,1)))"
          }
        },
        {
          "table": {
            "table_name": "cscart_category_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "testdobroded.cscart_categories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "0.10",
              "prefix_cost": "7.35",
              "data_read_per_join": "4K"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category",
              "ab__custom_category_h1"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_seo_names",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code",
              "company_id"
            ],
            "key_length": "210",
            "ref": [
              "testdobroded.cscart_categories.category_id",
              "const",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "0.10",
              "prefix_cost": "7.70",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "name",
              "object_id",
              "company_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}

Result

category_id parent_id id_path category position status company_id storefront_id seo_name seo_path ab__lc_catalog_image_control ab__custom_category_h1
118 32 32/118 Натуральная косметика 0 A 1 1 naturalnaya-kosmetika 32 none
1 0 1 Варенье и Джем 10 A 1 1 varenie left Варенье традиционное домашнее или экзотическое вкусное и полезное!
130 129 129/130 Ежовик 10 A 1 1 ezhovik 129 none
59 0 59 Урбеч, Паста, Мед, Халва 15 A 1 1 urbech-pasta-med-halva none
131 129 129/131 Соль 20 A 1 1 sol 129 none
4 0 4 Эко сладости и Снэки 25 A 1 1 ecosladosti none
132 129 129/132 Масло 30 A 1 1 maslo 129 none
19 0 19 Чаи и Ягодные напитки 30 A 1 1 econapitki none
23 0 23 Подарки и Сувениры 35 A 1 1 podarochnye-nabory left Набор эко продуктов - это отличный подарок к любому празднику
136 129 129/136 Соусы 40 A 1 1 sousy 129 none
57 0 57 Ягоды и Фрукты 45 A 1 1 yagody-i-frukty none
32 0 32 Товары для дома и бани 55 A 1 1 tovary-dlya-doma none
33 1 1/33 Домашнее варенье 1010 A 1 1 domashnee-varene 1 none Домашнее варенье из Карелии это натуральный качественный экопродукт
49 1 1/49 Варенье без сахара 1020 A 1 1 varene-bez-sahara 1 none
129 0 129 Соль и пряности 1022 A 1 1 pischevye-dobavki none
48 1 1/48 Традиционное варенье 1030 A 1 1 tradicionnoe-varene 1 none
153 0 153 Сувениры 1032 A 1 1 suveniry none
112 1 1/112 Комплекты варенья 1090 A 1 1 komplekty-varenya 1 none
133 1 1/133 Классическое варенье 1100 A 1 1 klassicheskoe-varene 1 none
134 1 1/134 Джемы классические 1110 A 1 1 dzhemy-klassicheskie 1 none
135 1 1/135 Варенье из белых грибов 1120 A 1 1 vaene-iz-belyh-gribov 1 none
61 59 59/61 Мёд 1520 A 1 1 med 59 none
53 4 4/53 Шоколад, Конфеты, Леденцы 2510 A 1 1 shokolad-konfety-ledency 4 none
54 4 4/54 Пастила, Зефир, Мармелад 2530 A 1 1 marmelad-zefir-pastila 4 none
6 19 19/6 Иван-чай 3010 A 1 1 ivan-chay 19 left
74 19 19/74 Морсы, Кисели, Компоты 3020 A 1 1 morsy-kiseli-kompoty 19 none
50 23 23/50 Подарочные наборы 3510 A 1 1 podarochnye-nabory 23 none
58 57 57/58 Ягоды сушеные 4520 A 1 1 yagody-sushenye 57 none
93 32 32/93 Банные принадлежности 5510 A 1 1 bannye-prinadlezhnosti 32 none