SELECT 
  categories.category_id, 
  categories.parent_id, 
  categories.id_path, 
  category_descriptions.category, 
  categories.position, 
  categories.status, 
  categories.company_id, 
  categories.storefront_id, 
  seo_names.name as seo_name, 
  seo_names.path as seo_path, 
  category_descriptions.mega_m_category_banner_url, 
  category_descriptions.mega_m_category_svg_icon 
FROM 
  categories 
  LEFT JOIN category_descriptions ON categories.category_id = category_descriptions.category_id 
  AND category_descriptions.lang_code = 'en' 
  LEFT JOIN seo_names ON seo_names.object_id = categories.category_id 
  AND seo_names.type = 'c' 
  AND seo_names.dispatch = '' 
  AND seo_names.lang_code = 'en' 
WHERE 
  1 = 1 
  AND (
    categories.usergroup_ids = '' 
    OR FIND_IN_SET(0, categories.usergroup_ids) 
    OR FIND_IN_SET(1, categories.usergroup_ids)
  ) 
  AND categories.status IN ('A') 
  AND categories.storefront_id IN (0, 1) 
  AND categories.deleted_at IS NULL 
  AND categories.category_id IN(
    3, 
    5, 
    22, 
    25, 
    27, 
    28, 
    8, 
    39, 
    42, 
    4, 
    45, 
    52, 
    62, 
    71, 
    73, 
    75, 
    76, 
    78, 
    87, 
    96, 
    97, 
    105, 
    106, 
    108, 
    109, 
    112, 
    114, 
    115, 
    116, 
    117, 
    118, 
    2, 
    16, 
    143, 
    178, 
    179, 
    183, 
    194, 
    46, 
    195, 
    205, 
    206, 
    208, 
    100, 
    210, 
    211, 
    228, 
    51, 
    250, 
    265, 
    266, 
    275, 
    21, 
    336, 
    2896, 
    2528, 
    2529, 
    2530, 
    2532, 
    2890, 
    2891, 
    2893, 
    2894, 
    2895, 
    2898, 
    2899, 
    2900, 
    2901, 
    2902, 
    2903, 
    2909, 
    2911, 
    2913, 
    2914, 
    2915, 
    2916, 
    2918, 
    2919, 
    2920, 
    2921, 
    2928, 
    2929, 
    2930, 
    2932, 
    2943, 
    2944, 
    2945, 
    2946, 
    2947, 
    2948, 
    2949, 
    2531, 
    2978, 
    2951, 
    2952, 
    2953, 
    2954, 
    2955, 
    2956, 
    2957, 
    2958, 
    2959, 
    2960, 
    2961, 
    2962, 
    2963, 
    2964, 
    2965, 
    2966, 
    2967, 
    2968, 
    2969, 
    2970, 
    2971, 
    2972, 
    2973, 
    2974, 
    2975, 
    2977, 
    2979, 
    2980, 
    2981, 
    2982, 
    2983, 
    2984, 
    2985, 
    2986, 
    2892, 
    2987, 
    2988, 
    2989, 
    2991, 
    2993, 
    2995, 
    3007, 
    3008, 
    3009, 
    3010, 
    3011, 
    3012, 
    3014, 
    3015, 
    3016, 
    3017, 
    3018, 
    6658, 
    92, 
    6672, 
    6790, 
    6792, 
    7760, 
    11369, 
    11372, 
    11373, 
    11374
  ) 
  AND categories.company_id = 7 
ORDER BY 
  categories.is_trash asc, 
  categories.position asc, 
  category_descriptions.category asc

Query time 0.00400

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "69.97"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "p_category_id",
              "index_categories_on_deleted_at"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 155,
            "rows_produced_per_join": 0,
            "filtered": "0.03",
            "cost_info": {
              "read_cost": "69.90",
              "eval_cost": "0.01",
              "prefix_cost": "69.90",
              "data_read_per_join": "134"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "company_id",
              "usergroup_ids",
              "status",
              "position",
              "is_trash",
              "deleted_at",
              "storefront_id"
            ],
            "attached_condition": "((`goimagine`.`categories`.`company_id` = 7) and ((`goimagine`.`categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`goimagine`.`categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`goimagine`.`categories`.`usergroup_ids`))) and (`goimagine`.`categories`.`status` = 'A') and (`goimagine`.`categories`.`storefront_id` in (0,1)) and (`goimagine`.`categories`.`deleted_at` is null) and (`goimagine`.`categories`.`category_id` in (3,5,22,25,27,28,8,39,42,4,45,52,62,71,73,75,76,78,87,96,97,105,106,108,109,112,114,115,116,117,118,2,16,143,178,179,183,194,46,195,205,206,208,100,210,211,228,51,250,265,266,275,21,336,2896,2528,2529,2530,2532,2890,2891,2893,2894,2895,2898,2899,2900,2901,2902,2903,2909,2911,2913,2914,2915,2916,2918,2919,2920,2921,2928,2929,2930,2932,2943,2944,2945,2946,2947,2948,2949,2531,2978,2951,2952,2953,2954,2955,2956,2957,2958,2959,2960,2961,2962,2963,2964,2965,2966,2967,2968,2969,2970,2971,2972,2973,2974,2975,2977,2979,2980,2981,2982,2983,2984,2985,2986,2892,2987,2988,2989,2991,2993,2995,3007,3008,3009,3010,3011,3012,3014,3015,3016,3017,3018,6658,92,6672,6790,6792,7760,11369,11372,11373,11374)))"
          }
        },
        {
          "table": {
            "table_name": "category_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "goimagine.categories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.01",
              "eval_cost": "0.01",
              "prefix_cost": "69.92",
              "data_read_per_join": "232"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category",
              "mega_m_category_banner_url",
              "mega_m_category_svg_icon"
            ]
          }
        },
        {
          "table": {
            "table_name": "seo_names",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code"
            ],
            "key_length": "206",
            "ref": [
              "goimagine.categories.category_id",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.04",
              "eval_cost": "0.01",
              "prefix_cost": "69.97",
              "data_read_per_join": "86"
            },
            "used_columns": [
              "name",
              "object_id",
              "company_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}