CODEWITHSUNDEEP
mysqlsqlinner-join
Kiwi_gurl
Kiwi_gurl

Reputation: 1

How can I improve this inner join query?

My database has 3 tables. One is called Customer, one is called Orders, and one is called RMA. The RMA table has the info regarding returns. I'll include a screen shot of all 3 so you can see the appropriate attributes. This is the code of the query I'm working on:

SELECT State, SKU, count(*) from Orders INNER JOIN Customer ON Orders.Customer_ID = Customer.CustomerID INNER JOIN RMA ON Orders.Order_ID = RMA.Reason Group by SKU Order by SKU LIMIT 10;

I'm trying to get how much of each product(SKU) is returned in each state(State). Any help would really be appreciated. I'm not sure why, but anytime I include a JOIN statement, my query takes anywhere from 5 minutes to 20 minutes to process.

Orders table[ Customer table]

!2[ RMA table]

!3

Upvotes: 0

Views: 122

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269983

Your query should look like this:

SELECT c.State, o.SKU, COUNT(*)
FROM Orders o INNER JOIN
     Customer c
     ON o.Customer_ID = c.CustomerID JOIN
     RMA
     ON o.Order_ID = RMA.Order_Id
GROUP BY c.State, o.SKU
ORDER BY SKU;

Your issue is probably the incorrect JOIN condition between Orders and RMA.

If you have primary keys properly declared on the tables, then this query should have good-enough performance.

Upvotes: 1

CheeseFerret
CheeseFerret

Reputation: 617

Given you are joining with an Orders table I'm going to assume this table contains all the orders that the company has ever done. This can be quite large and would likely cause the slowness you are seeing.

You can likely improve this query if you place some constraint on the Orders you are selecting, restricting what date range you use is common way to do this. If you provide more information about what the query is for and how large the dataset is everyone will be able to provide better guidance as to what filters would work best.

Upvotes: 0

Related Questions