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(
    2, 
    4, 
    5, 
    8, 
    15, 
    17, 
    18, 
    23, 
    25, 
    26, 
    45, 
    46, 
    47, 
    48, 
    51, 
    52, 
    71, 
    73, 
    76, 
    78, 
    81, 
    82, 
    3, 
    87, 
    91, 
    90, 
    98, 
    100, 
    106, 
    109, 
    112, 
    118, 
    13, 
    131, 
    132, 
    147, 
    169, 
    170, 
    179, 
    186, 
    187, 
    191, 
    194, 
    195, 
    196, 
    197, 
    199, 
    205, 
    257, 
    260, 
    266, 
    99, 
    271, 
    272, 
    278, 
    12, 
    55, 
    305, 
    59, 
    317, 
    319, 
    326, 
    2185, 
    2566, 
    5843, 
    7710, 
    6247, 
    6358, 
    6365, 
    6366, 
    6368, 
    6369, 
    6370, 
    6483, 
    6667, 
    6692, 
    97, 
    6726, 
    40, 
    6738, 
    6739, 
    6817, 
    6866, 
    8498, 
    6903, 
    7221, 
    7265, 
    7266, 
    7405, 
    22740, 
    7651, 
    7695, 
    7712, 
    7713, 
    7714, 
    7905, 
    8031, 
    8443, 
    8488, 
    8489, 
    8499, 
    8694, 
    8695, 
    9148, 
    9321, 
    10161, 
    10163, 
    10333, 
    10334, 
    10335, 
    10336, 
    10409, 
    10482, 
    10812, 
    10853, 
    11350, 
    13321, 
    13356, 
    13357, 
    13358, 
    13359, 
    13579, 
    13628, 
    14094, 
    15094, 
    15466, 
    16173, 
    18857, 
    18877, 
    18878, 
    18879, 
    18880, 
    18881, 
    18886, 
    19509, 
    19514, 
    19515, 
    19803, 
    20143, 
    20302, 
    20611, 
    20955
  ) 
  AND categories.company_id = 2948 
ORDER BY 
  categories.is_trash asc, 
  categories.position asc, 
  category_descriptions.category asc

Query time 0.00926

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "64.11"
    },
    "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": 142,
            "rows_produced_per_join": 0,
            "filtered": "0.04",
            "cost_info": {
              "read_cost": "64.03",
              "eval_cost": "0.00",
              "prefix_cost": "64.04",
              "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` = 2948) 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 (2,4,5,8,15,17,18,23,25,26,45,46,47,48,51,52,71,73,76,78,81,82,3,87,91,90,98,100,106,109,112,118,13,131,132,147,169,170,179,186,187,191,194,195,196,197,199,205,257,260,266,99,271,272,278,12,55,305,59,317,319,326,2185,2566,5843,7710,6247,6358,6365,6366,6368,6369,6370,6483,6667,6692,97,6726,40,6738,6739,6817,6866,8498,6903,7221,7265,7266,7405,22740,7651,7695,7712,7713,7714,7905,8031,8443,8488,8489,8499,8694,8695,9148,9321,10161,10163,10333,10334,10335,10336,10409,10482,10812,10853,11350,13321,13356,13357,13358,13359,13579,13628,14094,15094,15466,16173,18857,18877,18878,18879,18880,18881,18886,19509,19514,19515,19803,20143,20302,20611,20955)))"
          }
        },
        {
          "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.02",
              "eval_cost": "0.00",
              "prefix_cost": "64.06",
              "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": "64.11",
              "data_read_per_join": "86"
            },
            "used_columns": [
              "name",
              "object_id",
              "company_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}