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

Query time 0.00153

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.67"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_june_setupcategories",
            "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": 7,
            "rows_produced_per_join": 0,
            "filtered": "1.19",
            "cost_info": {
              "read_cost": "7.00",
              "eval_cost": "0.02",
              "prefix_cost": "8.40",
              "data_read_per_join": "222"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "company_id",
              "storefront_id",
              "usergroup_ids",
              "status",
              "position",
              "is_trash"
            ],
            "attached_condition": "(((`vishalecarter_june_setup`.`cscart_june_setupcategories`.`usergroup_ids` = '') or find_in_set(0,`vishalecarter_june_setup`.`cscart_june_setupcategories`.`usergroup_ids`) or find_in_set(1,`vishalecarter_june_setup`.`cscart_june_setupcategories`.`usergroup_ids`)) and (`vishalecarter_june_setup`.`cscart_june_setupcategories`.`status` = 'A') and (`vishalecarter_june_setup`.`cscart_june_setupcategories`.`id_path` like '167/%') and (`vishalecarter_june_setup`.`cscart_june_setupcategories`.`category_id` <> 264) and (`vishalecarter_june_setup`.`cscart_june_setupcategories`.`storefront_id` in (0,1)) and (`vishalecarter_june_setup`.`cscart_june_setupcategories`.`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_june_setupcategory_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "vishalecarter_june_setup.cscart_june_setupcategories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.08",
              "eval_cost": "0.02",
              "prefix_cost": "8.50",
              "data_read_per_join": "258"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_june_setupseo_names",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code"
            ],
            "key_length": "206",
            "ref": [
              "vishalecarter_june_setup.cscart_june_setupcategories.category_id",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.16",
              "eval_cost": "0.02",
              "prefix_cost": "8.68",
              "data_read_per_join": "144"
            },
            "used_columns": [
              "name",
              "object_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}