CODEWITHSUNDEEP
mysql
Steve T
Steve T

Reputation: 1

Find duplicate line items for each order

I have one table (orders) that has all the line items of each distinct order. I want to find out if any order has duplicate items (model) on it.

This is my working example which returns the proper values but only looks at one order and it takes 34 seconds to execute. I need to look at ~100 orders a day

SELECT ordnum, model
FROM orders
GROUP BY ordnum, model
HAVING (COUNT( * ) >1 && ordnum='1459387781')

I do have another table (orderinfo) where each record is one ordnum if needed to use.

There has to be a better way to query a table that has 31400 distinct ordnum but 1.3 million records any guidance?

Upvotes: 0

Views: 36

Answers (2)

Andreas
Andreas

Reputation: 159225

The statement does a full table scan

If you truly want it to only query one order, and the table has an index on ordnum (which seems likely), specify that in the WHERE clause, in which case there's no need to include ordnum in the GROUP BY:

SELECT model
FROM orders
WHERE ordnum = '1459387781'
GROUP BY model
HAVING COUNT(*) > 1

If you "need to look at ~100 orders a day", and you want to check todays orders, and your orderinfo table has an orderdate, then do this:

SELECT ordnum, model
FROM orders
WHERE ordnum in ( SELECT o.ordnum
                  FROM orderinfo o
                  WHERE o.orderdate = CURDATE() )
GROUP BY ordnum, model
HAVING COUNT(*) > 1

Will be very fast if orderinfo has index on orderdate and orders has index on ordnum.

Upvotes: 0

xQbert
xQbert

Reputation: 35343

All you need here is to group by ordNum, Model and return records having a count of model > 1 then..

HAVING executes after the select, so you have the count of records having the same model in an order.

The difference here is intead of count(*) which counts all records, we count the specific MODEL's

SELECT ORDNUM, model, count(Model)
FROM ORDERS
GROUP BY ORDNUM, MODEL
HAVING count(Model) > 1

As to the performance, if OrderNum and Model are part of a composite index this should be pretty quick.

Upvotes: 1

Related Questions