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 (248) 
  AND q_categories.id_path LIKE '248/%' 
  AND q_categories.category_id IN(
    12, 50, 77, 79, 604, 91, 99, 6, 159, 167, 
    190, 200, 248, 257, 264, 266, 273, 276, 
    278, 290, 302, 303, 323, 329, 330, 352, 
    361, 364, 391, 392, 393, 207, 469, 470, 
    475, 544, 546, 555, 598
  ) 
ORDER BY 
  q_categories.is_trash asc, 
  q_categories.position asc, 
  q_category_descriptions.category asc

Query time 0.00261

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.32"
    },
    "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": 11,
            "rows_produced_per_join": 0,
            "filtered": "0.45",
            "cost_info": {
              "read_cost": "11.00",
              "eval_cost": "0.01",
              "prefix_cost": "13.20",
              "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/%') and (`portal`.`q_categories`.`category_id` in (12,50,77,79,604,91,99,6,159,167,190,200,248,257,264,266,273,276,278,290,302,303,323,329,330,352,361,364,391,392,393,207,469,470,475,544,546,555,598)))"
          }
        },
        {
          "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": "13.26",
              "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": "13.32",
              "data_read_per_join": "86"
            },
            "used_columns": [
              "name",
              "object_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}