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 
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.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.00938

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.175362175,
    "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": "ALL",
              "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
              "loops": 1,
              "rows": 113,
              "cost": 0.028190165,
              "filtered": 75.22123718,
              "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.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": 85,
              "rows": 1,
              "cost": 0.14717201,
              "filtered": 100,
              "attached_condition": "trigcond(tmall_category_descriptions.lang_code = 'en')"
            }
          }
        ]
      }
    }
  }
}