Multiple left join hangs server
MySQL's query optimizer is not very smart, anything you can do to help it will go a long way to improving your query performance.

Couple of issues I see at a glance without being able to see an execution plan (please edit your question with the output of explain select * from table...:


Your first join is going from an indexed column on your left table (product_categories.product_id) to a non-indexed column of your right table (orders_products.product_id). With no where clause, that's going to result in a very heavy nested loop through every record on the orders_product for each row in your product_categories table. To speed it up you could add an index to orders_products.product_id, ideally you would also limit the left table using some sort of criteria in a where clause.
Your second join is doing a similar mistake. Your left table column is not indexed while your right column is a PK. I think you're probably signing up for another long queue of nested loops through each of the tables.


The fact that you're left joining also means that regardless of what's in your right table, you're going to have to inspect every element in your left table. Inner joins can be more performant, but without indexing your columns properly and without the use of some kind of where clause criteria to help limit the rowsets you have to inspect, this is going to be a heavy query.

In summary:


index your join columns
use inner joins where possible
use limiting criteria in your where clause if possible ti reduce the amount of index scanning needed.