Re: sub query is extermely slow

[SGreen]

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

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 
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:
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]

RE: sub query is extermely slow

[Andy Eastham]

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 <= ""

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

[SGreen]

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

[Artem Koltsov]

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 ' 
> > 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 ' (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 ' (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 

Re: sub query is extermely slow

[sam wun]

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 ' 
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 ' (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 ' (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

[Clint Edwards]

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 ' 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 ' (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 ' (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

[Clint Edwards]

Sam,
Can you send the following information:
When was the last time 'analyze table ' (inventory, transaction, 
customer) was executed?

OS:
MySQL Version:
Available Ram:
Output from 'SHOW CREATE TABLE ' (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 \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 wun]

Clint Edwards wrote:
Sam,
Can you send the output of the following:
#>explain \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

[Clint Edwards]

Sam,
Can you send the output of the following:
#>explain \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]

sub query is extermely slow

[sam wun]

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]