sql query improvements

I've changed a few of the sql queries (either by removing subqueries that weren't required or by changing subqueries to joins) in order to improve the query speed.

This is by no means an exhaustive list of changes, I'm sure I'll find more, this is just what was discovered by occasionally checking "show processlist" to see what was holding things up.