Multiple left join hangs server I have a problem with left join query. It connects 3 tables and calculates sales value for categories.

Query:

SELECT
    categories.name,
    categories.id,
    SUM((orders_products.product_price_gross + orders_products.option_price)*(1 - (orders_products.rebate/100))  * orders_products.product_qty) AS suma
FROM product_categories
LEFT JOIN orders_products 
    ON product_categories.product_id = orders_products.product_id
LEFT JOIN categories
    ON product_categories.category_id = categories.id
GROUP BY categories.name, categories.id
ORDER BY suma DESC


    categories
id  int(11) NO  PRI     NULL    auto_increment
parent_categories   int(11) NO      0   
name    varchar(250)    NO      

orders_products
id  int(11) NO  PRI     NULL    auto_increment
order_id    int(11) NO  PRI     0   
product_id  int(11) NO      0   
product_name    varchar(255)    NO          
product_price_gross     double(8,2) NO      0.00    
product_qty     int(3)  NO      0           
option_price    decimal(8,2)    NO      NULL    
discount    decimal(8,2)    NO      NULL    
rebate  int(11) NO      NULL    

product_categories
product_id  int(11) NO  MUL     0   
category_id     int(11) NO      0   
ord     int(11) NO      0   
id  int(11) NO  PRI     NULL    auto_increment


Tables are big - categories - 2808 rows, product_categories - 84 000, orders_products - 47500

It should display list of categories and sales values for each one. Instead of that it hangs the server totally and restart is needed. I used many different queries from StackOverflow but any of them hangs server. I have no idea why.
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.