How to optimize this nested SQL query

Question

Here is the database schema:

[redacted]

I'll describe what I'm doing with the query below:

Innermost query: Select all the saleIds satisfying the WHERE conditions

Middle query: Select all the productIds that were a part of the saleId

Outermost query: SUM the products.cost and select the vendors.name.

And here is the SQL query I came up with:

SELECT vendors.name AS Company
, SUM(products.cost) AS Revenue
FROM
    products
    INNER JOIN sold_products
        ON (products.productId = sold_products.productId)
    INNER JOIN vendors
    ON (products.vendorId = vendors.vendorId)
            WHERE sold_products.productId IN (
                    SELECT sold_products.productId
                    FROM
                        sold_products
                    WHERE sold_products.saleId IN (
                            SELECT sales.saleId     
                            FROM
                                markets
                                INNER JOIN vendors
                                ON (markets.vendorId = vendors.vendorId)
                                INNER JOIN sales_campaign
                                ON (sales_campaign.marketId = markets.marketId)
                                INNER JOIN packet_headers
                                ON (sales_campaign.packetHeaderId = packet_headers.packetHeaderId)
                                INNER JOIN packet_details
                                ON (packet_details.packetHeaderId = packet_headers.packetHeaderId)
                                INNER JOIN sales
                                ON (sales.packetDetailsId = packet_details.packetDetailsId)
                            WHERE vendors.customerId=60
                            )
                    )
GROUP BY Company
ORDER BY Revenue DESC;

Any help in optimizing this?

@Bohemian: EXPLAIN shows me a detailed table, but I'm afraid I don't understand what it means.. — Hrishikesh -Rishi- Choudhari
– Hrishikesh -Rishi- Choudhari, Commented Nov 11, 2012 at 13:11

Karl Heinz Matchinger · Accepted Answer · 2012-11-11 13:16:04Z

1

Since you are just using inner joins you normally simplify the query to smth like this:

SELECT  ve.name     AS Company
,       SUM(pr.cost) AS Revenue
FROM    products        pr
,       sold_products   sp
,       vendors         ve
,       markets         ma
,       sales_campaign  sc
,       packet_headers  ph
,       packet_details  pd
,       sales           sa
Where pr.productId = sp.productId
And   pr.vendorId  = ve.vendorId
And   ve.vendorId  = ma.vendorId
And   sc.marketId = ma.marketId
And   sc.packetHeaderId = ph.packetHeaderId
And   pd.packetHeaderId = ph.packetHeaderId)
And   sa.packetDetailsId = pd.packetDetailsId
And   ve.customerId = 60
GROUP BY ve.Company
ORDER BY pr.Revenue DESC;

Please try if this works and if it is faster and let me know.

answered Nov 11, 2012 at 13:16

Karl Heinz Matchinger

241 bronze badge

Sign up to request clarification or add additional context in comments.

6 Comments

Hrishikesh -Rishi- Choudhari Over a year ago

Heiz Matchinger: This is exactly what I was doing before, but the result was a completely wrong. This query would return SUM(products.cost) of ALL the products, whether they are sold or not..

Karl Heinz Matchinger Over a year ago

have you checked if it is really ALL products or if its just SUMs a product multiple times?! with the join to sold_products it can not SUM all products! Look at your query without the SUM => what is the result?

Hrishikesh -Rishi- Choudhari Over a year ago

Yes it really is a SUM of ALL the products by that Vendor; not the SUM of just the ones that were sold..

Karl Heinz Matchinger Over a year ago

Try to simplify for finding the bug: SELECT ve.name AS Company , SUM(pr.cost) AS Revenue FROM products pr , sold_products sp , vendors ve Where pr.productId = sp.productId And pr.vendorId = ve.vendorId And ve.customerId = 60 GROUP BY ve.Company ORDER BY pr.Revenue DESC;

Hrishikesh -Rishi- Choudhari Over a year ago

Wow that works perfectly! And instantaneously too!! Thanks :)

|

Collectives™ on Stack Overflow

How to optimize this nested SQL query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related