How can I improve this query?

select distinct a.PartNumber as 'Part Number', (
select count(1)
from Transactions b
where b.PartNumber = a.PartNumber) as 'No. Sold'
from listings a
order by a.PartNumber

It currently takes 30 seconds to run. Transactions has 1200 records and
listings has 7000.

Multiple listings can have the same part number, as can transactions.
We'd like to know how many transactions there are for each part number,
including those part numbers for which there are listings but no
transactions. Given the "and zero transactions" requirement, I can't
figure out how to do this query with a join.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How can I improve this query?

On 12/1/09 2:21 PM, "David Shere" wrote:

> select distinct a.PartNumber as 'Part Number', (
> select count(1)
> from Transactions b
> where b.PartNumber = a.PartNumber) as 'No. Sold'
> from listings a
> order by a.PartNumber
>
> It currently takes 30 seconds to run. Transactions has 1200 records and
> listings has 7000.
>
> Multiple listings can have the same part number, as can transactions.
> We'd like to know how many transactions there are for each part number,
> including those part numbers for which there are listings but no
> transactions. Given the "and zero transactions" requirement, I can't
> figure out how to do this query with a join.

how about using LEFT JOIN:

SELECT ...
FROM listings a
LEFT JOIN Transactions b ON b.PartNumber = a.PartNumber
....

wouldn't that tabulate also the unsold parts?

and for speed, does Transactions.PartNumber have an index?



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How can I improve this query?

At 01:21 PM 12/1/2009, David Shere wrote:
>select distinct a.PartNumber as 'Part Number', (
> select count(1)
> from Transactions b
> where b.PartNumber = a.PartNumber) as 'No. Sold'
>from listings a
>order by a.PartNumber
>
>It currently takes 30 seconds to run. Transactions has 1200 records and
>listings has 7000.
>
>Multiple listings can have the same part number, as can transactions. We'd
>like to know how many transactions there are for each part number,
>including those part numbers for which there are listings but no
>transactions. Given the "and zero transactions" requirement, I can't
>figure out how to do this query with a join.


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.

Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How can I improve this query?

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=gcdmg-mysql-2@m.gmane.org

Re: How can I improve this query?

At 10:13 AM 12/2/2009, you wrote:
>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.

The Counts are likely wrong because you are using Count(1). Don't forget
that the left join will join to the second table even if the b row does not
exist (returns all NULL's). Therefore you need to count only rows returned
from the 2nd table, correct? Then do something like
Sum(If(b.part=NULL,0,1)) so if there is no b row it sums 0, otherwise is
sums 1.

Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How can I improve this query?

On 12/2/09 11:13 AM, "David Shere" wrote:

> 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.)

gosh. unless you have 456,567 listings, i'm not sure how that could happen
other than through a CROSS JOIN or something.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org