SELECT 
  cscart_aprilcategories.category_id, 
  cscart_aprilcategories.parent_id, 
  cscart_aprilcategories.id_path, 
  cscart_aprilcategory_descriptions.category, 
  cscart_aprilcategories.position, 
  cscart_aprilcategories.status, 
  cscart_aprilcategories.company_id, 
  cscart_aprilcategories.storefront_id, 
  cscart_aprilseo_names.name as seo_name, 
  cscart_aprilseo_names.path as seo_path 
FROM 
  cscart_aprilcategories 
  LEFT JOIN cscart_aprilcategory_descriptions ON cscart_aprilcategories.category_id = cscart_aprilcategory_descriptions.category_id 
  AND cscart_aprilcategory_descriptions.lang_code = 'en' 
  LEFT JOIN cscart_aprilseo_names ON cscart_aprilseo_names.object_id = cscart_aprilcategories.category_id 
  AND cscart_aprilseo_names.type = 'c' 
  AND cscart_aprilseo_names.dispatch = '' 
  AND cscart_aprilseo_names.lang_code = 'en' 
WHERE 
  1 = 1 
  AND (
    cscart_aprilcategories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_aprilcategories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_aprilcategories.usergroup_ids
    )
  ) 
  AND cscart_aprilcategories.status IN ('A') 
  AND cscart_aprilcategories.parent_id IN (166) 
  AND cscart_aprilcategories.id_path LIKE '166/%' 
  AND cscart_aprilcategories.category_id != 264 
  AND cscart_aprilcategories.parent_id != 264 
  AND cscart_aprilcategories.storefront_id IN (0, 1) 
  AND cscart_aprilcategories.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, 
    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
  ) 
ORDER BY 
  cscart_aprilcategories.is_trash asc, 
  cscart_aprilcategories.position asc, 
  cscart_aprilcategory_descriptions.category asc

Query time 0.00165

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.43"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_aprilcategories",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "parent",
              "id_path",
              "p_category_id"
            ],
            "key": "parent",
            "used_key_parts": [
              "parent_id"
            ],
            "key_length": "3",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 6,
            "rows_produced_per_join": 0,
            "filtered": "1.19",
            "cost_info": {
              "read_cost": "6.00",
              "eval_cost": "0.01",
              "prefix_cost": "7.20",
              "data_read_per_join": "190"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "company_id",
              "storefront_id",
              "usergroup_ids",
              "status",
              "position",
              "is_trash"
            ],
            "attached_condition": "(((`vishalecarter_april_setup`.`cscart_aprilcategories`.`usergroup_ids` = '') or find_in_set(0,`vishalecarter_april_setup`.`cscart_aprilcategories`.`usergroup_ids`) or find_in_set(1,`vishalecarter_april_setup`.`cscart_aprilcategories`.`usergroup_ids`)) and (`vishalecarter_april_setup`.`cscart_aprilcategories`.`status` = 'A') and (`vishalecarter_april_setup`.`cscart_aprilcategories`.`id_path` like '166/%') and (`vishalecarter_april_setup`.`cscart_aprilcategories`.`category_id` <> 264) and (`vishalecarter_april_setup`.`cscart_aprilcategories`.`storefront_id` in (0,1)) and (`vishalecarter_april_setup`.`cscart_aprilcategories`.`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,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)))"
          }
        },
        {
          "table": {
            "table_name": "cscart_aprilcategory_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "vishalecarter_april_setup.cscart_aprilcategories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.07",
              "eval_cost": "0.01",
              "prefix_cost": "7.29",
              "data_read_per_join": "221"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_aprilseo_names",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code"
            ],
            "key_length": "206",
            "ref": [
              "vishalecarter_april_setup.cscart_aprilcategories.category_id",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.14",
              "eval_cost": "0.01",
              "prefix_cost": "7.44",
              "data_read_per_join": "124"
            },
            "used_columns": [
              "name",
              "object_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}