I think it might also be better to remove the function "date(tt.date)" if possible ie change
date(tt.date) <= "2005-01-31" to tt.date <= "<correct date format>" This will remove the function on the tt table field which I believe will force a full table scan on what is probably the largest table? Andy > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 19 January 2005 15:04 > To: sam wun > Cc: mysql@lists.mysql.com > Subject: Re: sub query is extermely slow > > sam wun <[EMAIL PROTECTED]> wrote on 01/19/2005 07:02:37 AM: > > > Hi list, > > > > The following sql statement takes 3 mintues to complete the query. How > > can I improve its speed? > > 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 > > > > Thanks > > Sam > > > > > > 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? > > 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]
