Sandbox

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(
    4, 
    8, 
    12, 
    23, 
    31, 
    32, 
    39, 
    45, 
    46, 
    47, 
    51, 
    52, 
    59, 
    5, 
    62, 
    71, 
    72, 
    73, 
    75, 
    76, 
    78, 
    2, 
    81, 
    82, 
    3, 
    87, 
    91, 
    90, 
    96, 
    101, 
    102, 
    106, 
    109, 
    110, 
    112, 
    115, 
    116, 
    118, 
    121, 
    123, 
    124, 
    127, 
    155, 
    183, 
    185, 
    191, 
    195, 
    198, 
    207, 
    208, 
    226, 
    231, 
    48, 
    241, 
    50, 
    244, 
    248, 
    250, 
    251, 
    252, 
    253, 
    179, 
    260, 
    266, 
    54, 
    297, 
    55, 
    305, 
    311, 
    318, 
    14, 
    379, 
    2185, 
    2566, 
    6634, 
    6657, 
    6659, 
    93, 
    6680, 
    26, 
    6691, 
    97, 
    6724, 
    41, 
    6775, 
    8954, 
    14412, 
    14409, 
    14410, 
    14411, 
    14417, 
    14413, 
    14440, 
    14414, 
    14415, 
    14416, 
    14419, 
    14422, 
    14420, 
    14421, 
    14424, 
    14425, 
    14426, 
    14428, 
    14429, 
    14430, 
    14431, 
    14432, 
    14433, 
    14434, 
    14435, 
    14436, 
    14441, 
    14442, 
    14443, 
    14444, 
    14445, 
    14447, 
    14448, 
    14449, 
    14450, 
    14451, 
    14452, 
    14453, 
    14455, 
    14456, 
    14457, 
    14458, 
    14460, 
    14461, 
    14462, 
    14655, 
    14656, 
    14676, 
    14856, 
    14858, 
    14859, 
    14860, 
    14862, 
    14863, 
    14865
  ) 
  AND categories.company_id = 6440 
ORDER BY 
  categories.is_trash asc, 
  categories.position asc, 
  category_descriptions.category asc

Query time 0.00424

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "63.65"
    },
    "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": 141,
            "rows_produced_per_join": 0,
            "filtered": "0.04",
            "cost_info": {
              "read_cost": "63.58",
              "eval_cost": "0.00",
              "prefix_cost": "63.59",
              "data_read_per_join": "133"
            },
            "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` = 6440) 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 (4,8,12,23,31,32,39,45,46,47,51,52,59,5,62,71,72,73,75,76,78,2,81,82,3,87,91,90,96,101,102,106,109,110,112,115,116,118,121,123,124,127,155,183,185,191,195,198,207,208,226,231,48,241,50,244,248,250,251,252,253,179,260,266,54,297,55,305,311,318,14,379,2185,2566,6634,6657,6659,93,6680,26,6691,97,6724,41,6775,8954,14412,14409,14410,14411,14417,14413,14440,14414,14415,14416,14419,14422,14420,14421,14424,14425,14426,14428,14429,14430,14431,14432,14433,14434,14435,14436,14441,14442,14443,14444,14445,14447,14448,14449,14450,14451,14452,14453,14455,14456,14457,14458,14460,14461,14462,14655,14656,14676,14856,14858,14859,14860,14862,14863,14865)))"
          }
        },
        {
          "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.00",
              "prefix_cost": "63.61",
              "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.00",
              "prefix_cost": "63.65",
              "data_read_per_join": "86"
            },
            "used_columns": [
              "name",
              "object_id",
              "company_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}

Result

category_id	parent_id	id_path	category	status	company_id	seo_name	seo_path
14865	14414	14440/14414/14865	Aeropress	A	6440	aeropress	14440/14414
14676	0	14676	Art and Craft	A	6440	art-en-10
14858	0	14858	Assistive	A	6440	assistive
14410	14412	14412/14410	Bathroom	A	6440	bathroom-en-4	14412
14453	14412	14412/14453	Bedroom	A	6440	bedroom	14412
14440	0	14440	Beverage	A	6440	beverage
14426	14422	14422/14426	Birthday	A	6440	birthday	14422
14413	14417	14417/14413	Board Game	A	6440	board-game	14417
14416	14414	14440/14414/14416	Breville	A	6440	breville	14440/14414
14461	14444	14444/14461	Cellphone	A	6440	cellphone	14444
14420	14422	14422/14420	Christmas	A	6440	christmas-en-44	14422
14435	0	14435	Cleaning	A	6440	cleaning-en
14414	14440	14440/14414	coffee	A	6440	coffee-en-3	14440
14433	14409	14412/14409/14433	Cookie Cutters	A	6440	cookie-cutters	14412/14409
14431	14411	14411/14431	Desk	A	6440	desk	14411
14452	14412	14412/14452	Dining	A	6440	dining	14412
14462	14445	14445/14462	Dremel	A	6440	dremel	14445
14436	14435	14435/14436	Dyson	A	6440	dyson	14435
14429	14422	14422/14429	Easter	A	6440	easter	14422
14444	0	14444	Electronics Accessories	A	6440	electronics-accessories
14415	14414	14440/14414/14415	Espresso	A	6440	espresso	14440/14414
14425	14422	14422/14425	Father's Day	A	6440	fathers-day	14422
14441	0	14441	Firearm Accessories	A	6440	firearm-accessories
14417	0	14417	Fun	A	6440	fun
14655	0	14655	Game Accessories	A	6440	game-accessories
14419	0	14419	Gifts	A	6440	gifts-en-11
14456	14447	14447/14456	Graduation	A	6440	graduation	14447
14428	14422	14422/14428	Halloween	A	6440	halloween	14422
14863	14422	14422/14863	Hanukkah	A	6440	hanukkah	14422
14656	0	14656	Hobby	A	6440	hobby
14422	0	14422	Holidays	A	6440	holidays-en-12
14412	0	14412	Home	A	6440	home-en-7
14450	0	14450	Kids	A	6440	kids-en-6
14409	14412	14412/14409	Kitchen	A	6440	kitchen-en-8	14412
14447	0	14447	Learning	A	6440	learning
14460	14447	14447/14460	Math	A	6440	math	14447
14424	14422	14422/14424	Mother's Day	A	6440	mothers-day	14422
14443	0	14443	Music	A	6440	music
14411	0	14411	Office	A	6440	office-en-2
14432	14411	14411/14432	organizers	A	6440	organizers	14411
14451	0	14451	Painting	A	6440	painting
14457	14412	14412/14457	Pets	A	6440	pets	14412
14859	0	14859	Pets	A	6440	pets-en-12
14856	0	14856	Plants	A	6440	plants-en-2
14455	14447	14447/14455	School	A	6440	school	14447
14434	0	14434	Signs	A	6440	signs-en-21
14862	0	14862	Spring and Summer	A	6440	spring-and-summer-en-3
14458	0	14458	Storage & Organization	A	6440	storage-and-organization
14430	14422	14422/14430	Thanksgiving	A	6440	thanksgiving	14422
14445	0	14445	Tools	A	6440	tools-en
14448	14417	14417/14448	Toys	A	6440	toys	14417
14442	0	14442	Travel	A	6440	travel-en-3
14449	0	14449	Trophies	A	6440	trophies
14421	14422	14422/14421	Valentines Day	A	6440	valentines-day-en-15	14422
14860	0	14860	Winter and Fall	A	6440	winter-and-fall