Optimize MySQL Query with multiple subqueries
Add an index to (post_id) in postlocks, remove the subquery against that table and the reference to that column in HAVING, and add WHERE NOT EXISTS (SELECT * FROM postlocks pl WHERE pl.post_id = p.id).

...for a start.

You want that condition to be evaluated early since it will eliminate some of the other lookups, and this should help ensure that the optimizer does that.  And the column needs to be indexed, otherwise you're scanning the table, as indicated by type = ALL in dependent subquery 2.