Re: sub query is extermely slow

[sam wun]

[EMAIL PROTECTED] wrote:
I would simplify it by converting everything to us explicit (not 
implicit) JOIN statements,skipping unnecessary type conversions,  and 
logically merging your conditions. Here is your original query, 
slightly reformatted.

SELECT DISTINCT i.basename
FROM inventory i, transaction t, customer c
WHERE i.prodcode = t.prodcode
        and c.custcode = t.custcode
        and i.basename is not NULL
        and i.prodname is not NULL
        and (
                (date(t.date) >= "2004-01-01"
                and date(t.date) <= "2004-01-31"
        )
        and i.basename IN (
                select DISTINCT ii.basename
                from inventory ii, transaction tt, customer cc
               where ii.prodcode = tt.prodcode
                and cc.custcode = tt.custcode
                and ii.basename is not NULL
                and ii.prodname is not NULL
                and(
                        date(tt.date) >= "2005-01-01"
                        and date(tt.date) <= "2005-01-31")
                )
        )
order by i.basename
Here is my proposal:
SELECT DISTINCT i.basename
FROM inventory i
INNER JOIN transaction t
        ON i.prodcode = t.prodcode
        AND t.date >= '2004-01-01'
        AND t.date <= '2004-01-31'
INNER JOIN transaction tt
        ON i.prodcode = tt.prodcode
        AND tt.date >= '2005-01-01'
        AND tt.date <= '2005-01-31'
INNER JOIN customer c
        ON c.custcode = t.custcode
        AND c.custcode = tt.custcode
WHERE i.basename is not NULL
        and i.prodname is not NULL
order by i.basename
This should give you a list of inventory basenames for all current 
customers (their names are still in the customer table) that "had 
transactions" (ordered products?) during both JAN 2004 and JAN 2005. 
This list will show only the products that were ordered during BOTH 
time periods BY THE SAME CUSTOMER at least once (at least one repeat 
sale, year to year, in JAN). Is this what you were after or was there 
a different question you were trying to answer?
Hi Shawn, this works great. This will give all products that exist in 
two different period. What if I also want to list products that in 
period 1 but not in peirod 2; while other products in preriod 2 but not 
in period 1? Do you think simply take out the join for i.prodcode = 
t.prodcode and i.prodcode = tt.prodcode and use left/right join will 
yield the desired result?

Thanks
Sam
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]