CODEWITHSUNDEEP
mysqljoin
Timo002
Timo002

Reputation: 3208

MySQL JOIN and SUM performance issue

I'm having an issue with MySQL JOIN and SUM. Because my JOIN has multiple matches I will do a SELECT in the JOIN so it won't sum to much.

The problem:
My query is terribly slow. In the test setup below it runs in just 1ms without any indexes. But on my production database this query takes ~4 seconds when selecting just one single record (WHERE id = X).

Table a has ~700.000 records
Table b has ~800.000 records
Table c has ~45.000 records

Table a has index on id
Table b has index on id, a_id
Table c has index on id, a_id

Below the simplified tables and used Query.

Table a

id
---
1

Table b

id | a_id | amount | price
--------------------------
 1 |    1 |      1 |    25
 2 |    1 |      1 |    20

Table c

id | a_id | amount | price
--------------------------
 1 |    1 |      1 |    23
 2 |    1 |      1 |    20

The Query:
Now when I run this query to get the sum of b and c that have a reference in table a.

SELECT id, bPrice, cPrice
FROM a
LEFT JOIN (SELECT a_id, SUM(amount * price) AS bPrice FROM b GROUP BY a_id) bb ON a.id = bb.a_id
LEFT JOIN (SELECT a_id, SUM(amount * price) AS cPrice FROM c GROUP BY a_id) cc ON a.id = cc.a_id

Wrong result but fast

SELECT 
    a.id,
    SUM(b.amount * b.price) AS bPrice,
    SUM(c.amount * c.price) AS cPrice
FROM a
JOIN b ON a.id = b.a_id
JOIN c ON a.id = c.a_id;

SQL Fiddle example

Upvotes: 2

Views: 1157

Answers (3)

paul
paul

Reputation: 1279

Might be worth a try to subquery - sometimes they can be quicker:

SELECT 
    id, 
    (SELECT SUM(amount * price) AS bPrice FROM b where b.a_id = a.id) AS bPrice, 
    (SELECT SUM(amount * price) AS cPrice FROM c where c.a_id = a.id) AS cPrice
FROM a

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

MySQL will struggle to pull the index through from the sub queries to join against, and suspect you are joining a LOT of rows from the sub queries.

As an initial step, you could try removing one of the sub queries to just change it to a join against the main table.

SELECT id, 
        SUM(b.amount * b.price) AS bPrice, 
        cPrice
FROM a
LEFT OUTER JOIN b ON a.id = b.a_id
LEFT OUTER JOIN 
(
    SELECT a_id, 
            SUM(amount * price) AS cPrice 
    FROM c 
    GROUP BY a_id
) cc ON a.id = cc.a_id
GROUP BY a.id,
        cc.cPrice

Upvotes: 2

Amith Raj Shetty
Amith Raj Shetty

Reputation: 68

Try the following query:

select set1.id,set1.bPrice,set2.cPrice from (SELECT a.id,SUM(b.amount * b.price) AS bPrice FROM a JOIN b ON a.id = b.a_id) as set1
JOIN
(SELECT a.id,SUM(c.amount * c.price) AS cPrice FROM a JOIN c ON a.id = c.a_id) as set2 ON set1.id = set2.id

Upvotes: 0

Related Questions