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_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path, 
  srvd.service_id AS service_id, 
  srvd.title AS service_title, 
  cscart_categories.unit_size, 
  cat.service_type_id, 
  cat.product_template, 
  cat.office_offer_template, 
  cat.office_table_template 
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' 
  LEFT JOIN cscart_service_categories as srvc ON srvc.category_id = cscart_categories.category_id 
  LEFT JOIN cscart_service_descriptions as srvd ON srvd.service_id = srvc.service_id 
  AND srvd.lang_code = cscart_category_descriptions.lang_code 
  LEFT JOIN cscart_categories AS cat ON cat.category_id = cscart_category_descriptions.category_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) 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00378

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "cscart_categories.is_trash, cscart_categories.position, cscart_category_descriptions.category",
      "temporary_table": {
        "table": {
          "table_name": "cscart_categories",
          "access_type": "ALL",
          "possible_keys": ["c_status"],
          "rows": 142,
          "filtered": 100,
          "attached_condition": "(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` = 'A' and cscart_categories.storefront_id in (0,1)"
        },
        "table": {
          "table_name": "cscart_category_descriptions",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "9",
          "used_key_parts": ["category_id", "lang_code"],
          "ref": ["dev.cscart_categories.category_id", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_category_descriptions.lang_code = 'en')"
        },
        "table": {
          "table_name": "cscart_seo_names",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "dispatch"],
          "key": "PRIMARY",
          "key_length": "206",
          "used_key_parts": ["object_id", "type", "dispatch", "lang_code"],
          "ref": ["dev.cscart_categories.category_id", "const", "const", "const"],
          "rows": 8,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_seo_names.`type` = 'c' and cscart_seo_names.dispatch = '' and cscart_seo_names.lang_code = 'en')"
        },
        "table": {
          "table_name": "srvc",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["category_id"],
          "ref": ["dev.cscart_categories.category_id"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(srvc.category_id = cscart_categories.category_id)"
        },
        "table": {
          "table_name": "srvd",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "10",
          "used_key_parts": ["service_id", "lang_code"],
          "ref": [
            "dev.srvc.service_id",
            "dev.cscart_category_descriptions.lang_code"
          ],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(trigcond(cscart_category_descriptions.lang_code is not null) and trigcond(srvc.service_id is not null))"
        },
        "table": {
          "table_name": "cat",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "p_category_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["category_id"],
          "ref": ["dev.cscart_category_descriptions.category_id"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(trigcond(cscart_category_descriptions.category_id is not null))"
        }
      }
    }
  }
}

Result

category_id parent_id id_path category position status company_id storefront_id seo_name seo_path service_id service_title unit_size service_type_id product_template office_offer_template office_table_template
79 78 78/79 Boost 10 A 0 0 boost 78 1.00
70 69 69/70 Divine Orbs 10 A 0 0 divine-orbs 69 8 Currency 1.00 2 currency currency currency
114 113 113/114 Gil 10 A 0 0 gil 113 8 Currency 1.00 2 currency currency currency
136 60 60/136 Gold 10 A 0 0 gold 60 8 Currency 1.00 2 currency currency currency
105 104 104/105 Gold 10 A 0 0 gold 104 1.00
44 43 43/44 Gold 10 A 0 0 gold 43 8 Currency 1.00 2 currency currency currency
146 95 95/146 Gold 10 A 0 0 gold 95 1.00 2 currency currency currency
121 120 120/121 Gold 10 A 0 0 gold 120 1.00
154 119 119/154 Gold 10 A 0 0 gold 119 1.00
62 61 61/62 Gold 10 A 0 0 gold 61 1.00 2 currency currency currency
126 124 124/126 Gold 10 A 0 0 gold 124 1.00
97 96 96/97 Gold 10 A 0 0 gold 96 1.00
132 131 131/132 Gold 10 A 0 0 gold 131 1.00
35 34 34/35 Gold 10 A 0 0 gold 34 8 Currency 1.00 2 currency currency currency
102 101 101/102 Items 10 A 0 0 items 101 1.00
86 85 85/86 Keys 10 A 0 0 keys 85 1.00
161 160 160/161 Orbs 10 A 0 0 orbs 160 1.00 2 currency currency currency
34 0 34 World of Warcraft Classic 10 A 0 0 world-of-warcraft-classic 1.00
80 78 78/80 Coins 20 A 0 0 coins 78 1.00
106 104 104/106 Crowns 20 A 0 0 crowns 104 1.00
162 160 160/162 Delirium 20 A 0 0 delirium 160 1.00 2 currency currency currency
87 85 85/87 Items 20 A 0 0 items 85 1.00
64 61 61/64 Items 20 A 0 0 items 61 1.00 1 boost boost boost
98 96 96/98 Items 20 A 0 0 items 96 1.00
133 131 131/133 Items 20 A 0 0 items 131 1.00
115 113 113/115 Keys 20 A 0 0 keys 113 1.00 5 boost boost boost
147 95 95/147 Keys 20 A 0 0 keys 95 1.00
36 34 34/36 Leveling 20 A 0 0 leveling 34 6 Leveling 1.00 3 boost boost boost
137 60 60/137 Leveling 20 A 0 0 leveling 60 6 Leveling 1.00 3 boost boost boost
75 69 69/75 Mirrors of Kalandra 20 A 0 0 upgrade 69 8 Currency 1.00 2 currency currency currency
45 43 43/45 Raids 20 A 0 0 raids 43 2 Boosts 1.00 3 boost boost boost
103 101 101/103 Services 20 A 0 0 services 101 1.00
122 120 120/122 Services 20 A 0 0 services 120 1.00
155 119 119/155 Services 20 A 0 0 services 119 1.00
125 124 124/125 Silver 20 A 0 0 silver 124 8 Currency 1.00
43 0 43 World of Warcraft 20 A 0 0 world-of-warcraft 1.00
99 96 96/99 Boosting 30 A 0 0 boosting 96 1.00
163 160 160/163 Breach 30 A 0 0 breach 160 1.00 2 currency currency currency
72 69 69/72 Chaos Orbs 30 A 0 0 chaos-orbs 69 8 Currency 1.00
63 61 61/63 Collecting 30 A 0 0 collecting 61 1.00
81 78 78/81 Donat 30 A 0 0 donat 78 1.00
156 119 119/156 Donat 30 A 0 0 donat 119 1.00
37 34 34/37 Dungeons 30 A 0 0 dungeons 34 2 Boosts 1.00 3 boost boost boost
138 60 60/138 Dungeons 30 A 0 0 dungeons 60 2 Boosts 1.00 3 boost boost boost
46 43 43/46 Dungeons 30 A 0 0 dungeons 43 2 Boosts 1.00 3 boost boost boost
116 113 113/116 Items 30 A 0 0 items 113 1.00 1 boost boost boost
127 124 124/127 Items 30 A 0 0 items 124 1.00
107 104 104/107 Keys 30 A 0 0 keys 104 1.00
148 95 95/148 Leveling 30 A 0 0 leveling 95 1.00
123 120 120/123 Other 30 A 0 0 other 120 1.00
88 85 85/88 Raids 30 A 0 0 raids 85 1.00
134 131 131/134 Services 30 A 0 0 services 131 1.00
60 0 60 World of Warcraft Mists of Pandaria Classic 30 A 0 0 world-of-warcraft-wotlk-classic 1.00 0 currency currency currency
82 78 78/82 Achievements 40 A 0 0 achievements 78 1.00
135 131 131/135 Donat 40 A 0 0 donat 131 1.00
164 160 160/164 Essence 40 A 0 0 essence 160 1.00 2 currency currency currency
71 69 69/71 Exalted Orbs 40 A 0 0 exalted-orbs 69 8 Currency 1.00
108 104 104/108 Items 40 A 0 0 items 104 1.00
149 95 95/149 Items 40 A 0 0 items 95 1.00 1 boost boost boost
65 61 61/65 Leveling 40 A 0 0 leveling 61 1.00
61 0 61 New World 40 A 0 0 new-world 1.00
157 119 119/157 Other 40 A 0 0 other 119 1.00
100 96 96/100 Other 40 A 0 0 other 96 1.00
89 85 85/89 Quests 40 A 0 0 quests 85 1.00
139 60 60/139 Raid 40 A 0 0 raids 60 2 Boosts 1.00 3 boost boost boost
38 34 34/38 Raids 40 A 0 0 raids 34 2 Boosts 1.00 3 boost boost boost
117 113 113/117 Services 40 A 0 0 services 113 1.00 3 boost boost boost
128 124 124/128 Services 40 A 0 0 services 124 1.00
47 43 43/47 Upgrades 40 A 0 0 upgrades 43 5 Items 1.00 3 boost boost boost
109 104 104/109 Boosting 50 A 0 0 boosting 104 1.00
90 85 85/90 Boosting 50 A 0 0 boosting 85 1.00
83 78 78/83 Coaching 50 A 0 0 coaching 78 1.00
66 61 61/66 Coaching 50 A 0 0 coaching 61 1.00
129 124 124/129 Donat 50 A 0 0 donat 124 1.00
48 43 43/48 Equipment 50 A 0 0 equipment 43 5 Items 1.00 1 boost boost boost
165 160 160/165 Omens 50 A 0 0 omens 160 1.00 2 currency currency currency
73 69 69/73 Orbs (other) 50 A 0 0 orbs-other 69 5 Items 1.00
118 113 113/118 Other 50 A 0 0 other 113 1.00 9 boost boost boost
69 0 69 Path of Exile 50 A 0 0 path-of-exile 1.00
39 34 34/39 Quests 50 A 0 0 quests 34 2 Boosts 1.00 3 boost boost boost
140 60 60/140 Quests 50 A 0 0 quests 60 2 Boosts 1.00 3 boost boost boost
150 95 95/150 Services 50 A 0 0 services 95 1.00 3 boost boost boost
166 160 160/166 Accounts 60 A 0 0 accounts 160 1.00 7 boost boost boost
76 69 69/76 Builds 60 A 0 0 builds 69 1.00
151 95 95/151 Builds 60 A 0 0 builds 95 1.00
67 61 61/67 Expeditions 60 A 0 0 expeditions 61 1.00
84 78 78/84 Other 60 A 0 0 other 78 1.00
130 124 124/130 Other 60 A 0 0 other 124 1.00
160 0 160 Path of Exile 2 60 A 0 0 path-of-exile-2 1.00 0
91 85 85/91 PvE 60 A 0 0 pve 85 1.00
40 34 34/40 PvP 60 A 0 0 pvp 34 2 Boosts 1.00 3 boost boost boost
141 60 60/141 PvP 60 A 0 0 pvp 60 2 Boosts 1.00 3 boost boost boost
49 43 43/49 PvP 60 A 0 0 pvp 43 2 Boosts 1.00 3 boost boost boost
110 104 104/110 Skins 60 A 0 0 skins 104 1.00
111 104 104/111 Builds 70 A 0 0 builds 104 1.00
85 0 85 Destiny 2 70 A 0 0 destiny-2 1.00
41 34 34/41 Equipment 70 A 0 0 equipment 34 5 Items 1.00 1 boost boost boost
74 69 69/74 Items 70 A 0 0 items 69 1.00
142 60 60/142 Items 70 A 0 0 items 60 5 Items 1.00 1 boost boost boost
167 160 160/167 Keys 70 A 0 0 keys 160 1.00 5 boost boost boost
68 61 61/68 Other 70 A 0 0 other 61 1.00
152 95 95/152 Platinum 70 A 0 0 platinum 95 2 Boosts 1.00
50 43 43/50 Professions 70 A 0 0 professions 43 2 Boosts 1.00 3 boost boost boost
92 85 85/92 PvP 70 A 0 0 pvp 85 1.00
95 0 95 Diablo 4 80 A 0 0 diablo-4 1.00 2 currency currency currency
168 160 160/168 Items 80 A 0 0 items 160 1.00 1 boost boost boost
42 34 34/42 Other 80 A 0 0 other 34 2 Boosts 1.00 3 boost boost boost
112 104 104/112 Other 80 A 0 0 other 104 1.00
153 95 95/153 Others 80 A 0 0 others 95 1.00
51 43 43/51 Reputation 80 A 0 0 reputation 43 2 Boosts 1.00 3 boost boost boost
159 69 69/159 Services 80 A 0 0 services 69 2 Boosts 1.00
93 85 85/93 Silver 80 A 0 0 silver 85 1.00
143 60 60/143 Time cards 80 A 0 0 time-cards 60 1 Accounts 1.00 5 boost boost boost
144 60 60/144 Achievements 90 A 0 0 achievements 60 2 Boosts 1.00 3 boost boost boost
169 160 160/169 Boosting 90 A 0 0 boosting 160 1.00 3 boost boost boost
96 0 96 Lost Ark 90 A 0 0 lost-ark 1.00
77 69 69/77 Other 90 A 0 0 other 69 1.00
94 85 85/94 Other 90 A 0 0 other 85 1.00
52 43 43/52 Quests 90 A 0 0 quests 43 2 Boosts 1.00 3 boost boost boost
53 43 43/53 Achievements 100 A 0 0 achievements 43 2 Boosts 1.00 3 boost boost boost
101 0 101 Elyon 100 A 0 0 elyon 1.00
145 60 60/145 Other 100 A 0 0 other 60 2 Boosts 1.00 9 boost boost boost
170 160 160/170 Services 100 A 0 0 services 160 1.00 3 boost boost boost
104 0 104 Elder Scrolls Online 110 A 0 0 elder-scrolls-online 1.00
171 160 160/171 Guides 110 A 0 0 guides 160 1.00 3 boost boost boost
158 60 60/158 Hats 110 A 0 0 hats 60 2 Boosts 1.00 9 boost boost boost
54 43 43/54 Items 110 A 0 0 items 43 5 Items 1.00 1 boost boost boost
172 160 160/172 Coaching 120 A 0 0 coaching 160 1.00 3 boost boost boost
55 43 43/55 Coaching 120 A 0 0 coaching 43 3 Coaching 1.00 3 boost boost boost
113 0 113 Final Fantasy XIV 120 A 0 0 final-fantasy-xiv 1.00
173 160 160/173 Builds 130 A 0 0 builds 160 1.00 3 boost boost boost
56 43 43/56 Dragonflight Keys 130 A 0 0 dragonflight-keys 43 1 Accounts 1.00 1 boost boost boost
119 0 119 Runescape 130 A 0 0 runescape 1.00
174 160 160/174 Mirrors of Kalandra 140 A 0 0 mirrors-of-kalandra 160 1.00 2 currency currency currency
120 0 120 Old School RuneScape 140 A 0 0 old-school-runescape 1.00
57 43 43/57 Time Cards 140 A 0 0 time-cards 43 1 Accounts 1.00 1 boost boost boost
124 0 124 Albion Online 150 A 0 0 albion-online 1.00
58 43 43/58 Donation 150 A 0 0 donation 43 1 Accounts 1.00 1 boost boost boost
175 160 160/175 Other 150 A 0 0 other 160 1.00 9 boost boost boost
131 0 131 ArcheAge Unchained 160 A 0 0 archeage-unchained 1.00
59 43 43/59 Other 160 A 0 0 other 43 2 Boosts 1.00 3 boost boost boost
78 0 78 Apex Legends 170 A 0 0 apex-legends 1.00