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

Query time 0.00165

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "6.20"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_multi_deccategories",
            "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": 5,
            "rows_produced_per_join": 0,
            "filtered": "1.19",
            "cost_info": {
              "read_cost": "5.00",
              "eval_cost": "0.01",
              "prefix_cost": "6.00",
              "data_read_per_join": "159"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "company_id",
              "storefront_id",
              "usergroup_ids",
              "status",
              "position",
              "is_trash"
            ],
            "attached_condition": "(((`vishalecarter_multi_dec`.`cscart_multi_deccategories`.`usergroup_ids` = '') or find_in_set(0,`vishalecarter_multi_dec`.`cscart_multi_deccategories`.`usergroup_ids`) or find_in_set(1,`vishalecarter_multi_dec`.`cscart_multi_deccategories`.`usergroup_ids`)) and (`vishalecarter_multi_dec`.`cscart_multi_deccategories`.`status` = 'A') and (`vishalecarter_multi_dec`.`cscart_multi_deccategories`.`id_path` like '166/174/%') and (`vishalecarter_multi_dec`.`cscart_multi_deccategories`.`category_id` <> 264) and (`vishalecarter_multi_dec`.`cscart_multi_deccategories`.`storefront_id` in (0,1)) and (`vishalecarter_multi_dec`.`cscart_multi_deccategories`.`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_multi_deccategory_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "vishalecarter_multi_dec.cscart_multi_deccategories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.06",
              "eval_cost": "0.01",
              "prefix_cost": "6.07",
              "data_read_per_join": "184"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_multi_decseo_names",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code"
            ],
            "key_length": "206",
            "ref": [
              "vishalecarter_multi_dec.cscart_multi_deccategories.category_id",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.11",
              "eval_cost": "0.01",
              "prefix_cost": "6.20",
              "data_read_per_join": "103"
            },
            "used_columns": [
              "name",
              "object_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}