When should one employ MySQL stored procedures?

In MySQL, stored procedures don't make queries run faster. But stored procs can be used to avoid network overhead from transferring result sets back and forth. So the best time to use stored procedures is when you have a complex operation that is going to require a multi-step process of fetching results from a first query, then doing subsequent queries based on the results. The more iterations of this, the more a stored proc can reduce the overhead. Regarding your profiled query that is spending a lot of time doing sorting, the performance problem might be fixed by using the right index. You haven't shared the query, so I can't guess what index might help. You might like to read my presentation http://www.slideshare.net/billkarwin/how-to-design-indexes-really which includes a method for identifying indexes that help sorting. Keep in mind it's possible that in some queries, you can't avoid the filesort. For example, if you sort by an expression or computed column instead of an indexed column. Or if you sort by multiple columns from different tables. Or if you sort by a column from a table that is not the first in the join order. Then the only possible solution is denormalization.

Here's an approach to the query that you can use to improve your sort performance: select x.*, j1.stuff_i_want, j2_stuff_i_want from (select <stuff I want in the query> from post_tab order by post_id limit 50) x, join_table_1 j1, join_table_2 j2 where x.join_stuff_1= j1.join_stuff and x.join_stuff_2 = j2.join_stuff order by x.post_id; (use outer or inner joins as appropriate, and use JOIN if you don't like commas; personally I'm old-school there :) This makes the join happen on only the sorted set you want, and may use post_id as a passive sort if it's a key.  Using it in a top-level join defeats the passive sort and triggers an active sort in mysql.  You  need the second outer order-by to make sure the query answering doesn't unsort the results. Also, a FROM-clause subquery is one of the few subquery types that MySQL doesn't mess up...

Well, not to nullify the main question, but I've narrowed down the issue to NOT outer joins, rather, time required to sort the retrieved data. MySQL profiling reveals: Status / Time starting 0.000026 checking query cache for query 0.000061 Opening tables 0.000015 System lock 0.000006 Table lock 0.000027 init 0.000034 optimizing 0.000021 statistics 0.000059 preparing 0.000033 executing 0.000005 Sorting result 7.221466 Sending data 0.000083 end 0.000006 end 0.000005 query end 0.000005 storing result in query cache 0.000005 freeing items 0.000015 closing tables 0.000007 logging slow query 0.000004 logging slow query 0.000051 cleaning up 0.000006