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 (330) 
  AND q_categories.id_path LIKE '248/302/330/%' 
  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.00114

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.52"
    },
    "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": 2,
            "rows_produced_per_join": 0,
            "filtered": "2.50",
            "cost_info": {
              "read_cost": "2.00",
              "eval_cost": "0.01",
              "prefix_cost": "2.40",
              "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/302/330/%') 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": "2.46",
              "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": "2.52",
              "data_read_per_join": "86"
            },
            "used_columns": [
              "name",
              "object_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}