SELECT 
  tmall_categories.category_id, 
  tmall_categories.parent_id, 
  tmall_categories.id_path, 
  tmall_category_descriptions.category, 
  tmall_categories.position, 
  tmall_categories.status, 
  tmall_categories.company_id, 
  tmall_categories.storefront_id, 
  tmall_categories.ab__lc_catalog_image_control, 
  tmall_categories.ab__fn_category_status, 
  tmall_categories.ab__fn_label_color, 
  tmall_categories.ab__fn_label_background, 
  tmall_categories.ab__fn_use_origin_image, 
  tmall_category_descriptions.ab__fn_label_text, 
  tmall_category_descriptions.ab__fn_label_show 
FROM 
  tmall_categories 
  LEFT JOIN tmall_category_descriptions ON tmall_categories.category_id = tmall_category_descriptions.category_id 
  AND tmall_category_descriptions.lang_code = 'en' 
WHERE 
  1 = 1 
  AND (
    tmall_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, tmall_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, tmall_categories.usergroup_ids
    )
  ) 
  AND tmall_categories.status IN ('A') 
  AND tmall_categories.parent_id IN (167) 
  AND tmall_categories.id_path LIKE '167/%' 
  AND tmall_categories.storefront_id IN (0, 1) 
  AND tmall_categories.category_id IN(
    167, 165, 168, 169, 170, 171, 172, 166, 
    175, 176, 177, 178, 179, 180, 181, 182, 
    196, 199, 185, 186, 187, 188, 189, 174, 
    190, 191, 194, 193, 195, 197, 198, 200, 
    201, 202, 203, 211, 212, 204, 208, 209, 
    210, 213, 214, 215, 216, 217, 218, 219, 
    220, 221, 222, 223, 224, 225, 226, 227, 
    228, 229, 230, 231, 232, 234, 235, 236, 
    237, 238, 240, 241, 242, 243, 244, 245, 
    246, 247, 248, 249, 250, 251, 252, 253, 
    254, 263, 255, 293, 294
  ) 
ORDER BY 
  tmall_categories.is_trash asc, 
  tmall_categories.position asc, 
  tmall_category_descriptions.category asc

Query time 0.01798

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.006431477,
    "filesort": {
      "sort_key": "tmall_categories.is_trash, tmall_categories.position, tmall_category_descriptions.category",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "tmall_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"],
              "rowid_filter": {
                "range": {
                  "key": "id_path",
                  "used_key_parts": ["id_path"]
                },
                "rows": 7,
                "selectivity_pct": 6.194690265
              },
              "loops": 1,
              "rows": 7,
              "cost": 0.004497651,
              "filtered": 4.659722805,
              "attached_condition": "(tmall_categories.usergroup_ids = '' or find_in_set(0,tmall_categories.usergroup_ids) or find_in_set(1,tmall_categories.usergroup_ids)) and tmall_categories.`status` = 'A' and tmall_categories.id_path like '167/%' and tmall_categories.storefront_id in (0,1) and tmall_categories.category_id in (167,165,168,169,170,171,172,166,175,176,177,178,179,180,181,182,196,199,185,186,187,188,189,174,190,191,194,193,195,197,198,200,201,202,203,211,212,204,208,209,210,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,234,235,236,237,238,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,263,255,293,294)"
            }
          },
          {
            "table": {
              "table_name": "tmall_category_descriptions",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "9",
              "used_key_parts": ["category_id", "lang_code"],
              "ref": ["tmall.tmall_categories.category_id", "const"],
              "loops": 1,
              "rows": 1,
              "cost": 0.001933826,
              "filtered": 100,
              "attached_condition": "trigcond(tmall_category_descriptions.lang_code = 'en')"
            }
          }
        ]
      }
    }
  }
}