For the homework portlet query, could you please:
1. Try it without the "order by..." clause - you seem to indicate that this is the problem. How much time does the query take with the "order by"? How much without?
2. Could you post the EXPLAIN output for that query?
3. Here's the relevant query in content_item__new, could you test this, tell us how slow it is, and post the EXPLAIN output for it, after substituting in the proper parameter values, please?
select i2.name, tree_level(i2.tree_sortkey) as tree_level
from cr_items i1, cr_items i2
where i2.parent_id <> 0
and i1.item_id = get_path__item_id
and i1.tree_sortkey between i2.tree_sortkey and tree_right(i2.tree_sortkey)
order by tree_level
