Tom Worster wrote:
> how about using LEFT JOIN:
>
> SELECT ...
> FROM listings a
> LEFT JOIN Transactions b ON b.PartNumber = a.PartNumber
This gives me a result set of 456,567 lines. I'm looking for a result
set of 60-70 lines. (That's how many part numbers we have.)
> and for speed, does Transactions.PartNumber have an index?
Done... I didn't think that you could do that for columns where
duplicates were allowed.
mos wrote:
> Do the Left Join as the other person said and also replace the
> "Distinct" "Order By" with "Group by PartNumber" and you won't need the
> sort.
SELECT a.PartNumber, count(1)
FROM listings a
LEFT JOIN Transactions b ON b.PartNumber = a.PartNumber
group by a.PartNumber
This gives results that almost look right (66 lines), however there are
some unrealistically high numbers. The transactions totals for each
part number, when added together, come to 545,325. This is obviously
wrong, because there are only 7000 transactions.
My original query does exactly what I want it to; it just takes 30
seconds to run. Whatever improvement (if any is possible) I make to the
query would need to produce the same results. Someone has already done
this comparison by hand, and we've eliminated the listings with no
transactions, so I can't run this again to see if it still comes up with
part numbers for which there are listings but no transactions. It would
still be useful to know how to speed up the query in the future, though.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
