CREATE TEMPORARY TABLE node_count (uid int, nodes int);
INSERT INTO node_count
SELECT users.uid, count(*) as nodes
FROM users
LEFT JOIN users_roles ON
(
 users_roles.rid IN (3,4,5)
 AND users_roles.uid=users.uid
)
INNER JOIN node_revisions ON (node_revisions.uid=users.uid)
GROUP BY users.uid;

CREATE TEMPORARY TABLE comment_count (uid int, comments int);
INSERT INTO comment_count
SELECT users.uid, count(*) as comments
FROM users
LEFT JOIN users_roles ON
(
 users_roles.rid IN (3,4,5)
 AND users_roles.uid=users.uid
)
INNER JOIN comments ON (comments.uid=users.uid)
GROUP BY users.uid;

SELECT 
 users.name, 
 if(node_count.nodes is NULL,0,node_count.nodes) as nodes, 
 if(comment_count.comments is NULL,0,comment_count.comments) as comments,
 (if(node_count.nodes is NULL,0,node_count.nodes) + if(comment_count.comments is NULL,0,comment_count.comments)) as combined
FROM 
users 
LEFT JOIN node_count ON (users.uid=node_count.uid)
LEFT JOIN comment_count ON (users.uid=comment_count.uid)
WHERE (comments!=0 OR nodes!=0)
ORDER BY combined DESC;