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 
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' 
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.parent_id IN (166) 
  AND cscart_categories.id_path LIKE '166/%' 
  AND cscart_categories.category_id != 265 
  AND cscart_categories.parent_id != 265 
  AND cscart_categories.storefront_id IN (0, 1) 
  AND cscart_categories.category_id IN(
    167, 165, 168, 169, 170, 171, 172, 166, 
    175, 176, 177, 178, 179, 180, 181, 182, 
    185, 186, 187, 188, 189, 174, 190, 191, 
    193, 194, 195, 196, 197, 198, 199, 200, 
    201, 202, 203, 204, 208, 209, 210, 211, 
    212, 213, 214, 215, 216, 217, 218, 219, 
    220, 221, 222, 223, 224, 225, 226, 227, 
    264, 228, 229, 230, 231, 232, 234, 235, 
    236, 237, 238, 240, 241, 243, 244, 245, 
    246, 247, 248, 249, 250, 251, 252, 253, 
    254, 263, 255
  ) 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00501

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "cscart_categories.is_trash, cscart_categories.position, cscart_category_descriptions.category",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "c_status",
                "parent",
                "id_path",
                "p_category_id"
              ],
              "key": "parent",
              "key_length": "3",
              "used_key_parts": ["parent_id"],
              "ref": ["const"],
              "rows": 6,
              "filtered": 37.91358185,
              "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.id_path like '166/%' and cscart_categories.category_id <> 265 and cscart_categories.storefront_id in (0,1) and cscart_categories.category_id in (167,165,168,169,170,171,172,166,175,176,177,178,179,180,181,182,185,186,187,188,189,174,190,191,193,194,195,196,197,198,199,200,201,202,203,204,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,264,228,229,230,231,232,234,235,236,237,238,240,241,243,244,245,246,247,248,249,250,251,252,253,254,263,255)"
            }
          },
          {
            "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": ["cscart.cscart_categories.category_id", "const"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(cscart_category_descriptions.lang_code = 'ru')"
            }
          }
        ]
      }
    }
  }
}