Mysql – Multiple left join hangs server

join;MySQLperformancequery-performance

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.

Best Answer

I am not so familiar with mysql. BUT. You should always connect relations on primary - foreign key. You join PRODUCT_CATEGORIES with ORDERS_PRODUCT on product_id which are foreign key and I am not sure if they can be NULL. Run EXPLAIN select and you will know what your query planner is doing.

Related Solutions

MySQL: Which join is better between left outer join and inner join

There is not a "better" or a "worse" join type. They have different meaning and they must be used depending on it.

In your case, you probably do not have employees with no work_log (no rows in that table), so LEFT JOIN and JOIN will be equivalent in results. However, if you had such a thing (a new employee with no registered work_log), a JOIN wold omit that employee, while a left join (whose first table is employees) would show all of them, and nulls on the fields from work_log if there are not matches.

Visual explanation of JOIN types
Image by C.L. Moffatt on Code Project

Again, performance is a secondary thing to query correctness. Some people say that you shouldn't use LEFT JOINs. It is true that a LEFT JOIN forces the optimizer to execute the query in one particular order, preventing some optimizations (table reordering) in some cases. Here is one example. But you should not choose one over the other if correctness/meaning is sacrificed, as an INNER JOIN is not inherently worse. The rest of the usual optimizations apply as usual.

In summary, do not use LEFT JOIN if you really mean INNER JOIN.

In MySQL CROSS JOIN, INNER JOIN and JOIN are the same. In the standard, and semantically, a CROSS JOIN is an INNER JOIN without an ON clause, so you get every combination of rows between tables.

You have examples of all semantic types of join on Wikipedia. In practice, in MySQL, we tend to only write JOIN and LEFT JOIN.

Mysql – Slow SQL query with LEFT JOIN

As a general rule, run (and post the results here) an EXPLAIN on your query to see if indexes are being used or not. At first, I see there are no other indexes on the sessions table except PKs, this will almost surely make your query run slow, since the WHERE will cause the RDBMS do a full table scan to find the rows.
I guess your query will at least require an index on latest_renewal (but, again, check with EXPLAIN).

This is my understanding of your query, on which I based my simplification:

SELECT sessions.member_id
    ,sessions.session_token
    ,sessions.username
    ,sessions.ip_address
FROM bot_sessions AS sessions
LEFT JOIN ( /* */
    SELECT stats.member_id
        ,stats.session_token
        ,stats.username
        ,stats.ip_address
    FROM bot_sessions_statistics AS stats
    WHERE date_active >= DATE (NOW()) /* a. I think this gives all of 'today' statistics */
    ) AS stats ON sessions.member_id = stats.member_id /* b. you're joining on 'member_id' which is NOT NULL in sessions, so any row from this subquery will have a value for (stats.)member_id */
WHERE latest_renewal >= DATE (NOW())
    AND stats.member_id IS NULL /* c. this will filter out all the rows from the JOIN, since no stats.member_id can be NULL */

Next, at first sight (if I'm not mistaken) I think your SELECT query can be rewritten without the LEFT JOIN, like this:

SELECT sessions.member_id, sessions.session_token, sessions.username, sessions.ip_address
FROM bot_sessions AS sessions
WHERE 
    NOT EXISTS (
        SELECT 1 FROM bot_sessions_statistics
        WHERE date_active >= date(NOW())
    )
AND latest_renewal >= date(NOW())

If the above is really correct, compare the results of EXPLAIN on both.

Best Answer

Related Solutions

MySQL: Which join is better between left outer join and inner join

Mysql – Slow SQL query with LEFT JOIN

Related Question