Re: sub query is extermely slow
[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 16 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]
Re: sub query is extermely slow
sam wun [EMAIL PROTECTED] wrote on 01/20/2005 11:45:40 AM: [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 16 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 Once you start getting into larger sets of data (millions of rows), Taking snapshots (as you did with your CREATE TABLE ... SELECT statements) can provide some significant performance boosts. Here is a numerical analysis of the two situations: Assume we have a table with 100 (1Millon = 1e6) rows in it that needs to be joined to itself for a query (much as in the query above). Computing the JOIN of two tables that each contain 1e6 rows could result in a virtual table containing up to 1e12 rows. The actual number of rows in a JOIN will be smaller than the pure Cartesian product because it will be limited to only those rows match your ON conditions. However the number 1e12 represents how many permutations of data the engine must decide between in order to calculate the results of the JOIN. Let's imagine that instead of needing to join the entire table to itself, that we only need to match 1 (1e4) rows of the table's data against another set of 1 rows (two nice round figures that could stand in for the actual number of transaction records during January of each year in our original query data). A JOIN between those two sets of rows would result in a maximum Cartesian product of only 1e8 rows. If it takes a fixed length of time (not a variable length of time) for the query engine to decide if any one row of a JOIN's Cartesian product belongs to the final JOIN results (based on evaluating the ON conditions against each row combination) then the subset JOIN will reduce the time it takes to compute the virtual table by a factor of 1 (1e12/1e8 = 1e4). That means that the query engine made 999,999,990,000 fewer comparisons to build the virtual table based on the JOINed sub-tables than it would need to build the same table based on the whole table joined to itself. Your results seem to support this analysis. The time you spent creating the subsets of data (time to select rows + time to write them into a table, twice) was much, MUCH less than the time it would take to sort through all of the other possible combinations of data in your original table that didn't need to participate in your analysis. As I said at the beginning, this is a query optimization technique for certain queries against larger datasets. For queries that deal with tables on the order of a few hundred thousand rows or less, it may not apply. Your mileage will vary. Only actual testing will reveal the actual performance of one technique over the other. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
sub query is extermely slow
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sub query is extermely slow
Sam, Can you send the output of the following: #explain your query\G Clint From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: sub query is extermely slow Date: Wed, 19 Jan 2005 20:02:37 +0800 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub query is extermely slow
Clint Edwards wrote: Sam, Can you send the output of the following: #explain your query\G Thanks for the suggestion, here is the output of the explain query: mysql explain 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; +++---++---+--+-+--+--+--+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra| +++---++---+--+-+--+--+--+ | 1 | PRIMARY| c | index | PRIMARY | PRIMARY | 32 | NULL | 317 | Using index; Using temporary; Using filesort | | 1 | PRIMARY| t | ref| custcode,prodcode | custcode | 32 | datacube.c.custcode | 36 | Using where | | 1 | PRIMARY| i | eq_ref | PRIMARY | PRIMARY | 32 | datacube.t.prodcode |1 | Using where | | 2 | DEPENDENT SUBQUERY | cc| index | PRIMARY | PRIMARY | 32 | NULL | 317 | Using index; Using temporary | | 2 | DEPENDENT SUBQUERY | tt| ref| custcode,prodcode | custcode | 32 | datacube.cc.custcode | 36 | Using where | | 2 | DEPENDENT SUBQUERY | ii| eq_ref | PRIMARY | PRIMARY | 32 | datacube.tt.prodcode |1 | Using where | +++---++---+--+-+--+--+--+ 6 rows in set (0.01 sec) Clint From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: sub query is extermely slow Date: Wed, 19 Jan 2005 20:02:37 +0800 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub query is extermely slow
Sam, Can you send the following information: When was the last time 'analyze table table_name' (inventory, transaction, customer) was executed? OS: MySQL Version: Available Ram: Output from 'SHOW CREATE TABLE table_name' (inventory, transaction, and customer): Output from SHOW VARIABLES LIKE '%buffer%';: Clint From: sam wun [EMAIL PROTECTED] To: Clint Edwards [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 20:39:41 +0800 Clint Edwards wrote: Sam, Can you send the output of the following: #explain your query\G Thanks for the suggestion, here is the output of the explain query: mysql explain 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; +++---++---+--+-+--+--+--+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra | +++---++---+--+-+--+--+--+ | 1 | PRIMARY| c | index | PRIMARY | PRIMARY | 32 | NULL | 317 | Using index; Using temporary; Using filesort | | 1 | PRIMARY| t | ref| custcode,prodcode | custcode | 32 | datacube.c.custcode | 36 | Using where | | 1 | PRIMARY| i | eq_ref | PRIMARY | PRIMARY | 32 | datacube.t.prodcode |1 | Using where | | 2 | DEPENDENT SUBQUERY | cc| index | PRIMARY | PRIMARY | 32 | NULL | 317 | Using index; Using temporary | | 2 | DEPENDENT SUBQUERY | tt| ref| custcode,prodcode | custcode | 32 | datacube.cc.custcode | 36 | Using where | | 2 | DEPENDENT SUBQUERY | ii| eq_ref | PRIMARY | PRIMARY | 32 | datacube.tt.prodcode |1 | Using where | +++---++---+--+-+--+--+--+ 6 rows in set (0.01 sec) Clint From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: sub query is extermely slow Date: Wed, 19 Jan 2005 20:02:37 +0800 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub query is extermely slow
Sam, Can you create an index on transaction.date, then run your query again? If that is not better send me the output of 'explain query' again. This index may not be a good idea, depending on how many transaction are in the table on a specified date. Clint From: sam wun [EMAIL PROTECTED] To: Clint Edwards [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 22:05:58 +0800 Clint Edwards wrote: Sam, Can you send the following information: When was the last time 'analyze table table_name' (inventory, transaction, customer) was executed? Hi, here is the result of the analyze command: mysql analyze table inventory,transaction, customer; +--+-+--+--+ | Table| Op | Msg_type | Msg_text | +--+-+--+--+ | datacube.inventory | analyze | status | OK | | datacube.transaction | analyze | status | OK | | datacube.customer| analyze | status | OK | +--+-+--+--+ 3 rows in set (0.83 sec) OS: MySQL Version: Mysql 5.0 Available Ram: 254RAM Output from 'SHOW CREATE TABLE table_name' (inventory, transaction, and customer): Output from SHOW VARIABLES LIKE '%buffer%';: mysql SHOW CREATE TABLE inventory; +---+---+ | Table | Create Table | +---+---+ | inventory | CREATE TABLE `inventory` ( `prodcode` varchar(32) NOT NULL default '', `qty` decimal(9,2) NOT NULL default '0.00', `lastupdatedate` date NOT NULL default '-00-00', `prodname` varchar(32) default 'UNKNOWN', `basename` varchar(32) default 'UNKNOWN', `vendorname` varchar(50) default 'UNKNOWN', `cost` decimal(9,2) NOT NULL default '0.00', PRIMARY KEY (`prodcode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+---+ 1 row in set (0.39 sec) mysql SHOW CREATE TABLE transaction; +-++ | Table | Create Table
Re: sub query is extermely slow
Clint Edwards wrote: Sam, Can you create an index on transaction.date, then run your query again? If that is not better send me the output of 'explain query' again. This index may not be a good idea, depending on how many transaction are in the table on a specified date. May I ask how to add index to a field after a table is created? Thanks Sam Clint From: sam wun [EMAIL PROTECTED] To: Clint Edwards [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 22:05:58 +0800 Clint Edwards wrote: Sam, Can you send the following information: When was the last time 'analyze table table_name' (inventory, transaction, customer) was executed? Hi, here is the result of the analyze command: mysql analyze table inventory,transaction, customer; +--+-+--+--+ | Table| Op | Msg_type | Msg_text | +--+-+--+--+ | datacube.inventory | analyze | status | OK | | datacube.transaction | analyze | status | OK | | datacube.customer| analyze | status | OK | +--+-+--+--+ 3 rows in set (0.83 sec) OS: MySQL Version: Mysql 5.0 Available Ram: 254RAM Output from 'SHOW CREATE TABLE table_name' (inventory, transaction, and customer): Output from SHOW VARIABLES LIKE '%buffer%';: mysql SHOW CREATE TABLE inventory; +---+---+ | Table | Create Table | +---+---+ | inventory | CREATE TABLE `inventory` ( `prodcode` varchar(32) NOT NULL default '', `qty` decimal(9,2) NOT NULL default '0.00', `lastupdatedate` date NOT NULL default '-00-00', `prodname` varchar(32) default 'UNKNOWN', `basename` varchar(32) default 'UNKNOWN', `vendorname` varchar(50) default 'UNKNOWN', `cost` decimal(9,2) NOT NULL default '0.00', PRIMARY KEY (`prodcode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+---+ 1 row in set (0.39 sec) mysql SHOW CREATE TABLE transaction; +-++ | Table | Create Table
RE: sub query is extermely slow
Check ALTER statement in MySQL doc. It explains how to add/modify an index after a table has been created. -Original Message- From: sam wun [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 19, 2005 10:00 AM Cc: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Clint Edwards wrote: Sam, Can you create an index on transaction.date, then run your query again? If that is not better send me the output of 'explain query' again. This index may not be a good idea, depending on how many transaction are in the table on a specified date. May I ask how to add index to a field after a table is created? Thanks Sam Clint From: sam wun [EMAIL PROTECTED] To: Clint Edwards [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 22:05:58 +0800 Clint Edwards wrote: Sam, Can you send the following information: When was the last time 'analyze table table_name' (inventory, transaction, customer) was executed? Hi, here is the result of the analyze command: mysql analyze table inventory,transaction, customer; +--+-+--+--+ | Table| Op | Msg_type | Msg_text | +--+-+--+--+ | datacube.inventory | analyze | status | OK | | datacube.transaction | analyze | status | OK | | datacube.customer| analyze | status | OK | +--+-+--+--+ 3 rows in set (0.83 sec) OS: MySQL Version: Mysql 5.0 Available Ram: 254RAM Output from 'SHOW CREATE TABLE table_name' (inventory, transaction, and customer): Output from SHOW VARIABLES LIKE '%buffer%';: mysql SHOW CREATE TABLE inventory; +---+- --- -- -- -- -- ---+ | Table | Create Table | +---+- --- -- -- -- -- ---+ | inventory | CREATE TABLE `inventory` ( `prodcode` varchar(32) NOT NULL default '', `qty` decimal(9,2) NOT NULL default '0.00', `lastupdatedate` date NOT NULL default '-00-00', `prodname` varchar(32) default 'UNKNOWN', `basename` varchar(32) default 'UNKNOWN', `vendorname` varchar(50) default 'UNKNOWN', `cost` decimal(9,2) NOT NULL default '0.00', PRIMARY KEY (`prodcode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+- --- -- -- -- -- ---+ 1 row in set (0.39 sec) mysql SHOW CREATE TABLE transaction
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
RE: sub query is extermely slow
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]
Re: sub query is extermely slow
[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]
