Sandbox

SELECT 
  q_categories.category_id, 
  q_categories.parent_id, 
  q_categories.id_path, 
  q_category_descriptions.category, 
  q_categories.position, 
  q_categories.status, 
  q_seo_names.name as seo_name, 
  q_seo_names.path as seo_path, 
  q_categories.ab__lc_catalog_image_control 
FROM 
  q_categories 
  LEFT JOIN q_category_descriptions ON q_categories.category_id = q_category_descriptions.category_id 
  AND q_category_descriptions.lang_code = 'en' 
  LEFT JOIN q_seo_names ON q_seo_names.object_id = q_categories.category_id 
  AND q_seo_names.type = 'c' 
  AND q_seo_names.dispatch = '' 
  AND q_seo_names.lang_code = 'en' 
WHERE 
  1 = 1 
  AND (
    q_categories.usergroup_ids = '' 
    OR FIND_IN_SET(0, q_categories.usergroup_ids) 
    OR FIND_IN_SET(1, q_categories.usergroup_ids)
  ) 
  AND q_categories.status IN ('A') 
  AND q_categories.parent_id IN (249) 
  AND q_categories.id_path LIKE '248/249/%' 
  AND q_categories.category_id IN(
    6, 
    12, 
    13, 
    14, 
    15, 
    35, 
    38, 
    42, 
    44, 
    46, 
    47, 
    48, 
    50, 
    51, 
    60, 
    7, 
    61, 
    63, 
    64, 
    68, 
    77, 
    79, 
    82, 
    83, 
    84, 
    88, 
    89, 
    604, 
    91, 
    95, 
    96, 
    97, 
    98, 
    99, 
    2, 
    144, 
    100, 
    113, 
    116, 
    117, 
    10, 
    136, 
    145, 
    147, 
    148, 
    151, 
    153, 
    154, 
    159, 
    160, 
    164, 
    166, 
    167, 
    168, 
    179, 
    180, 
    183, 
    184, 
    185, 
    186, 
    187, 
    189, 
    190, 
    191, 
    192, 
    193, 
    194, 
    195, 
    196, 
    197, 
    199, 
    200, 
    201, 
    202, 
    204, 
    206, 
    207, 
    217, 
    221, 
    224, 
    225, 
    241, 
    242, 
    245, 
    248, 
    249, 
    250, 
    251, 
    252, 
    253, 
    254, 
    255, 
    256, 
    257, 
    258, 
    260, 
    263, 
    264, 
    273, 
    274, 
    277, 
    278, 
    289, 
    290, 
    294, 
    296, 
    298, 
    299, 
    300, 
    301, 
    302, 
    303, 
    315, 
    330, 
    360, 
    361, 
    362, 
    364, 
    367, 
    368, 
    369, 
    370, 
    371, 
    375, 
    376, 
    377, 
    379, 
    382, 
    383, 
    384, 
    385, 
    386, 
    389, 
    393, 
    395, 
    400, 
    401, 
    402, 
    406, 
    410, 
    412, 
    413, 
    418, 
    419, 
    420, 
    421, 
    422, 
    424, 
    425, 
    426, 
    428, 
    429, 
    431, 
    433, 
    434, 
    435, 
    436, 
    438, 
    439, 
    442, 
    443, 
    444, 
    445, 
    446, 
    450, 
    454, 
    455, 
    459, 
    460, 
    465, 
    467, 
    468, 
    469, 
    470, 
    473, 
    484, 
    489, 
    493, 
    495, 
    496, 
    497, 
    498, 
    499, 
    508, 
    509, 
    512, 
    513, 
    514, 
    515, 
    516, 
    11, 
    523, 
    529, 
    519, 
    530, 
    531, 
    532, 
    533, 
    535, 
    536, 
    539, 
    543, 
    544, 
    545, 
    546, 
    548, 
    550, 
    551, 
    552, 
    554, 
    555, 
    556, 
    557, 
    558, 
    559, 
    560, 
    561, 
    562, 
    563, 
    564, 
    565, 
    566, 
    569, 
    570, 
    571, 
    572, 
    573, 
    576, 
    578, 
    577, 
    579, 
    580, 
    581, 
    582, 
    583, 
    584, 
    585, 
    135, 
    586, 
    587, 
    589, 
    592, 
    593, 
    596, 
    597, 
    598, 
    602, 
    606
  ) 
ORDER BY 
  q_categories.is_trash asc, 
  q_categories.position asc, 
  q_category_descriptions.category asc

Query time 0.00146

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "16.92"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "q_categories",
            "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": 14,
            "rows_produced_per_join": 0,
            "filtered": "0.36",
            "cost_info": {
              "read_cost": "14.00",
              "eval_cost": "0.01",
              "prefix_cost": "16.80",
              "data_read_per_join": "135"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "usergroup_ids",
              "status",
              "position",
              "is_trash",
              "ab__lc_catalog_image_control"
            ],
            "attached_condition": "(((`portal`.`q_categories`.`usergroup_ids` = '') or find_in_set(0,`portal`.`q_categories`.`usergroup_ids`) or find_in_set(1,`portal`.`q_categories`.`usergroup_ids`)) and (`portal`.`q_categories`.`status` = 'A') and (`portal`.`q_categories`.`id_path` like '248/249/%') and (`portal`.`q_categories`.`category_id` in (6,12,13,14,15,35,38,42,44,46,47,48,50,51,60,7,61,63,64,68,77,79,82,83,84,88,89,604,91,95,96,97,98,99,2,144,100,113,116,117,10,136,145,147,148,151,153,154,159,160,164,166,167,168,179,180,183,184,185,186,187,189,190,191,192,193,194,195,196,197,199,200,201,202,204,206,207,217,221,224,225,241,242,245,248,249,250,251,252,253,254,255,256,257,258,260,263,264,273,274,277,278,289,290,294,296,298,299,300,301,302,303,315,330,360,361,362,364,367,368,369,370,371,375,376,377,379,382,383,384,385,386,389,393,395,400,401,402,406,410,412,413,418,419,420,421,422,424,425,426,428,429,431,433,434,435,436,438,439,442,443,444,445,446,450,454,455,459,460,465,467,468,469,470,473,484,489,493,495,496,497,498,499,508,509,512,513,514,515,516,11,523,529,519,530,531,532,533,535,536,539,543,544,545,546,548,550,551,552,554,555,556,557,558,559,560,561,562,563,564,565,566,569,570,571,572,573,576,578,577,579,580,581,582,583,584,585,135,586,587,589,592,593,596,597,598,602,606)))"
          }
        },
        {
          "table": {
            "table_name": "q_category_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "portal.q_categories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.05",
              "eval_cost": "0.01",
              "prefix_cost": "16.86",
              "data_read_per_join": "155"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category"
            ]
          }
        },
        {
          "table": {
            "table_name": "q_seo_names",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code"
            ],
            "key_length": "206",
            "ref": [
              "portal.q_categories.category_id",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.05",
              "eval_cost": "0.01",
              "prefix_cost": "16.92",
              "data_read_per_join": "86"
            },
            "used_columns": [
              "name",
              "object_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}

Result

category_id	parent_id	id_path	category	position	status	seo_name	seo_path	ab__lc_catalog_image_control
250	249	248/249/250	Dolls & Accessories	10	A	dolls-and-accessories	248/249	none
251	249	248/249/251	Cars & Trucks	20	A	cars-and-trucks	248/249	none
252	249	248/249/252	Building & Blocks	30	A	building-and-blocks	248/249	none
253	249	248/249/253	Puzzles & Boardgames	40	A	puzzles-and-boardgames	248/249	none
254	249	248/249/254	Stuffed Animals & Plush	50	A	stuffed-animals-and-plush	248/249	none
255	249	248/249/255	Arts & Crafts	60	A	arts-and-crafts	248/249	none
256	249	248/249/256	Educational	70	A	educational	248/249	none
433	249	248/249/433	Money Banks	90	A	money-banks	248/249	none
434	249	248/249/434	Hobby	100	A	hobby	248/249	none
459	249	248/249/459	Action & Toy Figures	110	A	action-and-toy-figures	248/249	none
484	249	248/249/484	Toy Balls	130	A	toy-balls-en	248/249	none
533	249	248/249/533	Remote Control Toys	140	A	remote-control-toys	248/249	none
535	249	248/249/535	Drones	150	A	drones	248/249	none
579	249	248/249/579	Baby & Toddler Toys	160	A	baby-and-toddler-toys	248/249	none