Re: sub query is extermely slow

[sam wun]

[EMAIL PROTECTED] wrote:

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, while this is much faster than subquery, I found there is "cumsy" 
way faster than this series inner joins.
I dicovered that if I create two different temporary tables with "create 
view as select ..." for 2 differnet period, the join between this temp 
tables is also much faster than this series inner joins approach.  With 
160000 records in the transaction table, the series inner joins takes  
very long time (more than 15 minutes) give out a result.

Any comment?
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]