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 (168) 
  AND q_categories.id_path LIKE '6/168/%' 
  AND q_categories.category_id IN(
    2, 
    6, 
    12, 
    13, 
    14, 
    16, 
    17, 
    18, 
    23, 
    25, 
    27, 
    15, 
    37, 
    38, 
    40, 
    41, 
    42, 
    44, 
    46, 
    49, 
    50, 
    51, 
    58, 
    7, 
    61, 
    63, 
    64, 
    68, 
    71, 
    74, 
    76, 
    77, 
    79, 
    82, 
    84, 
    87, 
    88, 
    90, 
    604, 
    91, 
    92, 
    93, 
    94, 
    95, 
    96, 
    97, 
    144, 
    100, 
    106, 
    111, 
    113, 
    115, 
    124, 
    10, 
    134, 
    136, 
    138, 
    139, 
    141, 
    147, 
    148, 
    150, 
    151, 
    152, 
    153, 
    154, 
    159, 
    160, 
    163, 
    166, 
    167, 
    168, 
    169, 
    172, 
    176, 
    177, 
    179, 
    180, 
    183, 
    184, 
    185, 
    186, 
    187, 
    188, 
    189, 
    190, 
    191, 
    192, 
    193, 
    194, 
    195, 
    196, 
    197, 
    199, 
    200, 
    201, 
    202, 
    204, 
    206, 
    207, 
    217, 
    220, 
    221, 
    233, 
    234, 
    241, 
    243, 
    244, 
    248, 
    249, 
    250, 
    251, 
    252, 
    254, 
    255, 
    256, 
    257, 
    258, 
    261, 
    264, 
    266, 
    269, 
    271, 
    273, 
    274, 
    275, 
    276, 
    277, 
    278, 
    290, 
    293, 
    294, 
    296, 
    297, 
    298, 
    299, 
    301, 
    302, 
    303, 
    308, 
    313, 
    323, 
    329, 
    330, 
    352, 
    356, 
    360, 
    361, 
    362, 
    364, 
    367, 
    368, 
    369, 
    370, 
    371, 
    300, 
    375, 
    378, 
    379, 
    383, 
    384, 
    385, 
    386, 
    388, 
    389, 
    391, 
    401, 
    402, 
    410, 
    412, 
    413, 
    414, 
    417, 
    418, 
    419, 
    420, 
    422, 
    424, 
    425, 
    426, 
    428, 
    429, 
    430, 
    431, 
    432, 
    434, 
    435, 
    436, 
    437, 
    438, 
    439, 
    442, 
    443, 
    445, 
    446, 
    448, 
    450, 
    454, 
    455, 
    459, 
    460, 
    462, 
    465, 
    466, 
    469, 
    470, 
    471, 
    473, 
    475, 
    476, 
    477, 
    478, 
    481, 
    253, 
    489, 
    493, 
    495, 
    498, 
    499, 
    507, 
    509, 
    512, 
    513, 
    516, 
    518, 
    519, 
    520, 
    11, 
    523, 
    526, 
    527, 
    529, 
    532, 
    533, 
    535, 
    536, 
    537, 
    539, 
    540, 
    544, 
    545, 
    546, 
    547, 
    549, 
    550, 
    551, 
    555, 
    556, 
    557, 
    558, 
    559, 
    560, 
    561, 
    563, 
    564, 
    565, 
    566, 
    406, 
    567, 
    568, 
    570, 
    572, 
    576, 
    578, 
    577, 
    579, 
    581, 
    582, 
    584, 
    585, 
    135, 
    586, 
    587, 
    596, 
    597, 
    598, 
    601, 
    602, 
    603, 
    606
  ) 
ORDER BY 
  q_categories.is_trash asc, 
  q_categories.position asc, 
  q_category_descriptions.category asc

Query time 0.00134

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "22.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": 19,
            "rows_produced_per_join": 0,
            "filtered": "0.26",
            "cost_info": {
              "read_cost": "19.00",
              "eval_cost": "0.01",
              "prefix_cost": "22.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 '6/168/%') and (`portal`.`q_categories`.`category_id` in (2,6,12,13,14,16,17,18,23,25,27,15,37,38,40,41,42,44,46,49,50,51,58,7,61,63,64,68,71,74,76,77,79,82,84,87,88,90,604,91,92,93,94,95,96,97,144,100,106,111,113,115,124,10,134,136,138,139,141,147,148,150,151,152,153,154,159,160,163,166,167,168,169,172,176,177,179,180,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,199,200,201,202,204,206,207,217,220,221,233,234,241,243,244,248,249,250,251,252,254,255,256,257,258,261,264,266,269,271,273,274,275,276,277,278,290,293,294,296,297,298,299,301,302,303,308,313,323,329,330,352,356,360,361,362,364,367,368,369,370,371,300,375,378,379,383,384,385,386,388,389,391,401,402,410,412,413,414,417,418,419,420,422,424,425,426,428,429,430,431,432,434,435,436,437,438,439,442,443,445,446,448,450,454,455,459,460,462,465,466,469,470,471,473,475,476,477,478,481,253,489,493,495,498,499,507,509,512,513,516,518,519,520,11,523,526,527,529,532,533,535,536,537,539,540,544,545,546,547,549,550,551,555,556,557,558,559,560,561,563,564,565,566,406,567,568,570,572,576,578,577,579,581,582,584,585,135,586,587,596,597,598,601,602,603,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": "22.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": "22.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
169	168	6/168/169	Blenders	10	A	blenders	6/168	none
172	168	6/168/172	Food Processors	40	A	food-processors	6/168	none
176	168	6/168/176	Countertop Burners	80	A	countertop-burners	6/168	none
177	168	6/168/177	Coffee Machines	90	A	coffee-machines	6/168	none
179	168	6/168/179	Fans	110	A	fans	6/168	none
180	168	6/168/180	Vacuum Cleaner	120	A	vacuum-cleaner	6/168	none
183	168	6/168/183	Kitchen Tools & Accessories	150	A	kitchen-tools-and-accessories	6/168	none
188	168	6/168/188	Grills	160	A	grills	6/168	none
244	168	6/168/244	Kettles	170	A	kettles	6/168	none
481	168	6/168/481	Choppers	180	A	choppers	6/168	none
518	168	6/168/518	Electric Cookers	190	A	electric-cookers	6/168	none
529	168	6/168/529	Insect Killer	220	A	insect-killer	6/168	none