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 (11) 
  AND q_categories.id_path LIKE '2/11/%' 
  AND q_categories.category_id IN(
    2, 
    10, 
    11, 
    12, 
    13, 
    14, 
    44, 
    50, 
    51, 
    58, 
    84, 
    604, 
    91, 
    144, 
    100, 
    101, 
    102, 
    106, 
    113, 
    115, 
    117, 
    121, 
    130, 
    131, 
    132, 
    133, 
    134, 
    135, 
    136, 
    137, 
    138, 
    139, 
    140, 
    142, 
    145, 
    146, 
    6, 
    7, 
    147, 
    153, 
    154, 
    159, 
    164, 
    167, 
    168, 
    169, 
    170, 
    179, 
    181, 
    183, 
    184, 
    185, 
    189, 
    190, 
    193, 
    194, 
    196, 
    197, 
    198, 
    199, 
    201, 
    202, 
    203, 
    245, 
    246, 
    248, 
    249, 
    252, 
    46, 
    294, 
    296, 
    299, 
    301, 
    361, 
    362, 
    364, 
    366, 
    367, 
    368, 
    378, 
    382, 
    383, 
    384, 
    385, 
    386, 
    387, 
    389, 
    395, 
    396, 
    400, 
    401, 
    402, 
    458, 
    403, 
    404, 
    405, 
    412, 
    413, 
    191, 
    419, 
    420, 
    421, 
    422, 
    423, 
    424, 
    425, 
    427, 
    428, 
    429, 
    431, 
    432, 
    438, 
    439, 
    442, 
    445, 
    446, 
    448, 
    450, 
    454, 
    455, 
    457, 
    465, 
    468, 
    207, 
    469, 
    470, 
    473, 
    482, 
    253, 
    489, 
    513, 
    516, 
    523, 
    529, 
    544, 
    545, 
    549, 
    555, 
    560, 
    564, 
    565, 
    578, 
    582, 
    584, 
    586, 
    589, 
    590, 
    591
  ) 
ORDER BY 
  q_categories.is_trash asc, 
  q_categories.position asc, 
  q_category_descriptions.category asc

Query time 0.00683

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "4.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": 4,
            "rows_produced_per_join": 0,
            "filtered": "1.25",
            "cost_info": {
              "read_cost": "4.00",
              "eval_cost": "0.01",
              "prefix_cost": "4.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 '2/11/%') and (`portal`.`q_categories`.`category_id` in (2,10,11,12,13,14,44,50,51,58,84,604,91,144,100,101,102,106,113,115,117,121,130,131,132,133,134,135,136,137,138,139,140,142,145,146,6,7,147,153,154,159,164,167,168,169,170,179,181,183,184,185,189,190,193,194,196,197,198,199,201,202,203,245,246,248,249,252,46,294,296,299,301,361,362,364,366,367,368,378,382,383,384,385,386,387,389,395,396,400,401,402,458,403,404,405,412,413,191,419,420,421,422,423,424,425,427,428,429,431,432,438,439,442,445,446,448,450,454,455,457,465,468,207,469,470,473,482,253,489,513,516,523,529,544,545,549,555,560,564,565,578,582,584,586,589,590,591)))"
          }
        },
        {
          "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": "4.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": "4.92",
              "data_read_per_join": "86"
            },
            "used_columns": [
              "name",
              "object_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}