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.product_count, 
  tmall_categories.ab__lc_catalog_image_control, 
  tmall_companies.company 
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' 
  LEFT JOIN tmall_companies ON tmall_companies.company_id = tmall_categories.company_id 
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.category_id IN (241, 242) 
  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.00785

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.011538698,
    "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": "range",
              "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
              "key": "PRIMARY",
              "key_length": "3",
              "used_key_parts": ["category_id"],
              "loops": 1,
              "rows": 2,
              "cost": 0.004212994,
              "filtered": 100,
              "index_condition": "tmall_categories.category_id in (241,242) 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)",
              "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)"
            }
          },
          {
            "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": 2,
              "rows": 1,
              "cost": 0.003662852,
              "filtered": 100,
              "attached_condition": "trigcond(tmall_category_descriptions.lang_code = 'en')"
            }
          },
          {
            "table": {
              "table_name": "tmall_companies",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["company_id"],
              "ref": ["tmall.tmall_categories.company_id"],
              "loops": 2,
              "rows": 1,
              "cost": 0.003662852,
              "filtered": 100
            }
          }
        ]
      }
    }
  }
}